Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home



Mortgage interest formula

Posted by Michel Benarrosh on October 29, 2001 5:17 PM
I am looking for a formula that would allow me to compute total interests for, say:
Mortgage 10 years
Rate 7% per year
Monthly payment
Any amount

How do I find:
Total interests paid after 4 or 5 and 6 years
Balance due on principal after 4, 5 and 6 years.
The formulas in Excel ISPMT and IPMT do not cut it.

Is there a solution?

Thank you very much in advance.


Here's a suggestion

Posted by Tom Urtis on October 29, 2001 7:34 PM
Michel,

To do this sort of calculation, Excel has 2 functions: CUMIPMT, which returns cumulative interest between start & stop dates, and CUMPRINC, which returns the cumulative principal between stop & start dates.

These 2 functions are found only in the Analysis Toolpak, so they are turned on using the Add-Ins command from the Tools menu.

The syntax for both functions is
=CUMIPMT or CUMPRINC and then
(Rate,NumberOfPayments,PresentValue,StartPeriod,EndPeriod,Type).

An example set up:

In A1, enter the interest rate, if it is 7% enter .007.
In A2, enter number of periods, in your case 120 for 10 years.
In A3, enter the present value of the loan.
In A4, enter the first payment period (they start with 1).
In A5, enter the last period for your calculation (24 for the second year, 36 for the third year, etc.).
In A6 enter the payment timing type. Excel uses the numeral 0 for payments due by the end of the period (which would probably apply in your case), and 1 for payments made at the beginning of the period.

So, in A7 for cumulative interest, you can enter:
=CUMIPMT(A1,A2,A3,A4,A5,A6)

or CUMPRINC(A1,A2,A3,A4,A5,A6) for the principal.

Remember, you must add in the analysis toolpak (Tools > Add-Ins) for this to work.

Hope this helps.

Tom Urtis


You might want to look at http://www.geocities.com/krweaver/amort.xls

Posted by Paul on October 30, 2001 2:51 AM




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.