Is there a RATE function for single sum?

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Is there a built in function for calculating the interest rate for a single
sum?

IOW, is there a function to calculate i in PV =FV/(1+i)^n ?

I can do the math to solve for i but I want to know if there is a function
to do it for me.

The RATE function is for annuities. NOMINAL and EFFCT each take the other
as input. And there are several functions to calculate security yields given
specific dates.

But all I want is a function to calculate i for a single sum.

Is there such a function?
 
Is there a built in function for calculating the interest rate for a single
sum?

IOW, is there a function to calculate i in PV =FV/(1+i)^n ?

I can do the math to solve for i but I want to know if there is a function
to do it for me.

The RATE function is for annuities. NOMINAL and EFFCT each take the other
as input. And there are several functions to calculate security yields given
specific dates.

But all I want is a function to calculate i for a single sum.

Is there such a function?
Click to expand...

Use IRR for a single period, XIRR otherwise. Negate the PV in each case.
 
Thanks WldBill

But if I can only input FV and -PV into IRR, then n must be limited to 1.

For example, if I start with 100 and end with 500, the IRR formula gives me
an i of 400%. Technically this is true, the investment increased by 400%.

But this tells me nothing of the time involved. I need an annual i to make
it meaninngful.

In the example above, if it took 4 years to go from 100 to 500, then the
annual i is 49.53%, a big difference from the 400% IRR gives me.

To me it is strange that this functionality was omitted from Excel.
 
Perhaps the "Rate" function.

=RATE(4,0,-100,500)
-> 49.53%

I thought "single sum" was for one period also.
 
Check what I said again. To use XIRR, add a pair of dates by the -PV and
FV, and make that range the second argument. The third "guess" argument
is optional, and you can experiment with its effect, if any.

But if you know N, you might as well simply take the IRR
^(1/N) As that goes, the math to avoid using IRR is almost as simple :)
 
Thank you Dana.

Help says RATE is for an annuity but I didn't think to specify a PMT of 0.
When you do, Excel treats it as a single sum (lump sum).

Thanks again.
 
Hi. Glad it helps.
Just to mention, one can also omit the 0:

=RATE(4,,-100,500)

or...
=POWER(500/100,1/4)-1
 
Back
Top