Possible problem with PMT and FV functions

[Keith]

Hello,

I must be missing something -- here’s the question.

If I use the Excel PMT(rate, number of periods, beginning balance, 0,0)
function, I can obtain the payments that would be made from an annuity, given
the three parameters shown with the function above. The last two values are
for “future value†which is set to zero, and “type†which can be a zero or 1,
but I set to zero.

Once I obtain the payment, I should be able to reverse the calculation and
obtain the original value by using the FV(rate, periods, payments, present
value, type) function.

The problem is that once I use the PMT function, the reverse isn’t giving me
the correct value. For example, the following items…

PMT settings

Rate = .04
Periods = 3
Beginning Balance = 10,000
Last two variables set to zero
Results (payment) = $3,603.49

FV settings

Rate = .04
Periods = 3
Payments = $3,603.49
Last two variables set to zero
Result (future value) = $11,248.64


What is the explanation of the difference between these two?
Why isn’t the result of the FV function $10,000 in this example?

Keith

 

[Fred Smith]

You specified "beginning balance" in your PMT function, so you need to use
the PV function to obtain the original amount.

If you had specified future value in your PMT function, you can reverse
engineer it with FV.

Regards,
Fred

 

[Joe User]

Why isn’t the result of the FV function $10,000 in this example?

The biggest problem is: you should use PV, not FV, since you are trying to
determine the beginning value.

However, even with that correction, you might encounter some small
differences. You are sloppy with the signs (plus or minus) of the numbers
in your examples. So it is unclear if you are doing things correctly.

PMT settings [....]
Beginning Balance = 10,000
Last two variables set to zero
Results (payment) = $3,603.49

Either you mean that the beginning balance is -10,000, or you mean the PMT
result is -3,603.49.

[PV] settings [....]
Payments = $3,603.49
Last two variables set to zero
Result ([present] value) = $11,248.64

If the sign of Payment here is not the same as the PMT result, the sign of
the PV result will be different than what you used for beginning balance in
the PMT formula.

Also, unless you use exactly the value returned by PMT -- that is, reference
the cell with the PMT formula -- you might see some small difference between
the PV result and the beginning balance that you used in the PMT formula.

For example, PV(4%,3,-3603.49) returns about 10,000.01 instead of 10,000.00.
The reason is because the PMT result is not really "exactly" 3603.49.
Format the PMT result with more decimal places to see its true value.

On the other hand, that is a real-world constraint: the payment should be
computed by ROUND(PMT(4%,3,-10000),2). That might mean that the last
payment is not the same as the regular payment. But some lenders might
handle the situation differently.

This disparity is exacerbated by the fact that a lender might use ROUNDDOWN
or ROUNDUP instead of ROUND. Moreover, a lender might round(up/down) to
fewer decimal places or units, for example to the dollar or to a multiple of
25 or 50 cents.


----- original message -----