Finance functions discrepancy

[Steve Simons]

Note: I have previously posted this in NG
microsoft.public.excel.worksheetfunctions (note the lack of a 'dot'
before functions). I've had no response, and it looks like a very
queit NG, so have posted here. Hopefully someone can help . . . .


Hi

I am trying to calculate using the RATE and FV functions and find that
the results do not tally:-

Initially I did a RATE function as below:

RATE function:-

"time period
years" Payment Present Value Future Value Type
10 -1200.00 100,000 0 0

Interest rate: 7.75% =RATE(A2*12,B2,C2,D2,E2)*12


I assumed that, if I did a FV function with values corresponding to
the above, I would get a Future Value of zero. But no. I got a FV of
-19.18. Can anyone explain why this is please?

FV function

interest rate "time period
years" Payment Present Value Type
7.75% 10 -1200.00 100000.00 0

Future Value -19.18 =FV(A2/12,B2*12,C2,D2,E2)

Can anyone explain why this is please?

I may also post some more messages asking for help on finance
functions.

TIA

Steve

 

[sebcopla]

You are rounding your rate to 7.75% when it is actually
7.74797561352715000%. Also make sure you input FV = in the rate formula
and ont the FV of the formula.

 

[JE McGimpsey]

If you really have the RATE() function in A2, then you should get 0 as a
result of the FV() function call.

But your returned value indicates that you have the value 7.75% in A2.
That's what RATE() return displays, rounded to 2 decimals, but the
actual value returned is 7.74797561352715%.

Thus your discrepancy.

(If you have the RATE() formula in A2, check that you don't have the
Tools/Options/Calculation Precision as displayed checkbox checked.)

And yes, m.p.e.worksheetfunctions is denegrated in favor of this ng.

 

[Steve Simons]

Hi

Thank you both for the quick and perfectly spot-on response. I changed
the rate to be 7.747975613527150% and the FV returned zero, as I'd
hoped.

Cheers

Steve

 

[joeu2004]

I changed
the rate to be 7.747975613527150% and the FV returned zero, as I'd hoped.

Wrong conclusion, IMHO.

If you want the exact rate computed by RATE(), then use =RATE(....).
FYI, the above is number is not exact either; it is merely Excel's
representation up to 15 decimal places. Of course, that is more than
good enough for most computations. But it is the "principal" of the
matter .

OTOH, if you would like a "nice round" interest rate like 7.75%, you
could simply compute the last payment amount, for example:

=c2 + fv(a2/12, b2*12, c2, d2, e2)