Need longhand formulas for CUMIPMT and CUMPRINC functions in Excel

[unconvinced at pcg123 dot com]

I need a formula for Excel to calculate the cumulative interest paid on
a loan and a formula to calculate the cumulative principal as well.

I am exporting the data generated through a dynamic link to an Excel
spreadsheet. Regrettably, the program I am using doesn't support either
the CUMIPMT or CUMPRINC functions of Excel, so I need a longhand
equation.

While I have a basic understanding of Excel I have no idea how to go
about doing these calculations without using the functions Excel has
built in for this. Any help in the matter would be greatly appreciated.

Thanks in advance.

Mitchell Herman

 

[Dana DeLouis]

Hi. Not sure, but see if this is something along the lines of what you
want.
This example calculates total interest paid to a loan between start and end
periods.
To make the equation smaller, first calculate your monthly payment
separately:
Example:
Loan = 100,000
ir (interest rate) = .10 / 12
np (number of periods) = 30*12

pmt = (ir*(ir + 1)^np*Loan)/((ir + 1)^np - 1)

To calculate the first month's interest, set start & end to 1.
To Calculate the total first year's interest, set start=1, and end =12.
(Not sure if this was the equation you wanted for Cumipmt)

((pmt - ir*Loan)*(ir + 1)^start +
((ir*Loan - pmt)*(ir + 1)^end +
ir*pmt*(end - start + 1))*(ir + 1))/
(ir*(ir + 1))

Hope this helps in some way...