CUMIPMT

[sox]

Hi there
I'm still struggling with loan calculation. I need a formula please that
will return the amount of interest in the first year of a loan.

Using CUMIPMT I have eg.
Interest 5% per annum
5 year term (60 pays)
Capital borrowed 70,000
Basis 0

=CUMIPMT(5%/12,60,70000,1,12,0)

This works but I need to get the same result using more basic formula
because lots of our managers don't have access to the analysis add-in. I've
tried some of the things already suggested but can't get them to work.

Sox

 

[Paul Corrado]

With your data in A1 - A3 as follows

A1 - Annual interest rate
A2 - # Years
A3 - Loan Amount

Then this is the first years interest.

=-PMT(A1/12,A2*12,A3)*12-(A3-PV(A1/12,A2*12-12,PMT(A1/12,A2*12,A3)))

CUMIPMT is the same as total payments less principle paid in the period,
which in this case is the original loan amount less the loan value at the
end of the first year (which btw is the present value of the remaining
payments discounted at the interest rate on the loan)

HTH

PC

 

[sox]

This works, thank you so much. It does any year you want too!
Sox

 

[Paul Corrado]

As written with the reference to the initial loan balance it does NOT
calculate the cumulative interest for any year, ONLY THE FIRST YEAR.

To make it work for any year you need to replace the third occurrence of A3
with another PV that calculates the number of periods remaining at the
beginning of the period you wish to assess.

PC

 

[sox]

I got the answer for the second year by using your formula adapted and
adding the answer to the first formula which is in B13

=+-PMT($C$5/12,$C$4*12,$C$3)*24-($C$3-PV($C$5/12,$C$4*12-24,PMT($C$5/12,$C$4
*12,$C$3)))+B13

It gives the same answer as cumipmt did but not quite the same way as you
suggest below.

Sox