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.
Posted by Tom Urtis on October 29, 2001 7:34 PM
Here's a suggestion
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
Posted by Paul on October 30, 2001 2:51 AM
You might want to look at http://www.geocities.com/krweaver/amort.xls
