Rate formula in Excel

[Higgs Boson]

Can I use the rate formula where there is step in the loan repayment

PV = -1000
N = 3 years, £125 per year
N = 7 year , £110 per year
Rate = ??

 

[Bernie Deitrick]

So CERN has been looking in the wrong place all along, huh?


Anyway, you can work it out by an iterative process, or by using solver.

For example, but an initial rate guess into cell A1, say 3%. Then in B1, use the formula

=RATE(7,110,-FV(A1,3,125,-1000))

This part FV(A1,3,125,-1000) returns the value of the loan after three payments, say XXX, which is
then fed to this

=RATE(7,110,-XXX)

to calculate the rate for the last 7 years.

In another cell, enter the formula

=A1-B1

and use solver to set that cell to a value of 0 by changing cell A1. Your example will work out to
about 2.61% annual rate, and XXX is about 695.

 

[Fred Smith]

XIRR is the easiest way to calculate this. Create a range of the 11 cash
flows, with dates one year apart, then feed these ranges to XIRR. It will
calculate the rate for you.

Regards,
Fred.