interest calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Lotus 123 had a "cpmt" function to calculate payments on mortgages for canadian lenders. Is there an equivalent in Excel?
 
Visit

http://groups.google.com/advanced_group_search

Search for all of the words: canadian mortgage
in Newsgroup: *excel*

There you will find many articles and discussions about Canadian mortgages
and how to calculate them in Excel

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


DGL said:
Lotus 123 had a "cpmt" function to calculate payments on mortgages for
Click to expand...
canadian lenders. Is there an equivalent in Excel?
 
Hi DGL!

I believe that with Canadian mortgages the rate quoted is the Annual
Nominal rate compounded six monthly although the payments are made
monthly at the equivalent effective rate per month.

If I've got this wrong or you have something slightly different then
don't hesitate to post back.

Assume a quoted rate of 6.5% nominal compounded six monthly in A1
Loan of $100000 in A2
over 10 years in A3
Payments monthly

Equivalent monthly effective rate is:

A3:
=(1+A1/2)^(2/12)-1
Returns: 0.534474007549757%

The monthly payments are thus:
A4:
=PMT(A4,A3*12,A2,0,0)
Returns: -1131.09309815294

The negative indicates a payment "out" in return for the payment "in"
of the loan.

The main difficulty is the conversion of interest payments because
Excel "requires" an effective rate per period of time "counted" by
Nper. There are Analysis ToolPak EFFECTIVE and NOMINAL functions that
help but I tend to prefer custom functions. I can make those available
for of group requests but you'll find it all covered in John
Walkenbach's Formulas 2003.

Although the Excel financial functions are "universal" the methods
used in different countries vary. And in some countries like the UK
you have two systems running side by side (Banks quote Annual
Effective and use Annual Nominal compounded monthly; Building
Societies calculate an annual payment at a declared rate and divide
the result by 12). But all these are capable of being translated to
inputs that Excel will handle.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
DGL said:
Lotus 123 had a "cpmt" function to calculate payments on mortgages
Click to expand...
for canadian lenders. Is there an equivalent in Excel?
 
Back
Top