Back to Dates in Excel archive index

Back to archive home

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

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.

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

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.

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.