If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Possible problem with PMT and FV functions
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 |
#2
|
|||
|
|||
Possible problem with PMT and FV functions
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 "Keith" wrote in message ... 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 |
#3
|
|||
|
|||
Possible problem with PMT and FV functions
"Keith" wrote:
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 ----- "Keith" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|