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.
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
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:
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.