Home Loan Schedule
Principal $125,000
Rate 7.2%
Years 20
Payment $984.19 per month
Year Beginning Balance Ending Balance Total Paid
1 $125,000 11,810.24

My professor wants me to use the pv functon for the ending balance on come
up with this figure of 122,095.14. I have tried everything and can not come
up with this figure. I put 7.2%/12 for rate, 20*12 for nper and 984.19*12
for pmt. Please help

You will find that most people do not give direct answers to students, as
that is the whole point is to try to figure it out on your own.

I will give you 1 hint though, think ROUNDING.....
--
John C


"becky" wrote:



Home Loan Schedule
Principal $125,000
Rate 7.2%
Years 20
Payment $984.19 per month
Year Beginning Balance Ending Balance Total Paid
1 $125,000 11,810.24

My professor wants me to use the pv functon for the ending balance on come
up with this figure of 122,095.14. I have tried everything and can not come
up with this figure. I put 7.2%/12 for rate, 20*12 for nper and 984.19*12
for pmt. Please help

On Oct 13, 8:09*am, becky wrote:
My professor wants me to use the pv functon for the ending balance on come
up with this figure of 122,095.14. I have tried everything and can not come
up with this figure. I put 7.2%/12 for rate, 20*12 for nper and 984.19*12
for pmt. Please help

First, you would use FV, not PV, to compute an ending balance in this
situation.

Second, you would use the payment of 984.19 (approximately; more
later); you would not multiply that by 12, although you are correct in
using 7.2%/12.

Finally, I am not sure you fully understand the problem. The problem
is: given an original loan of $125,000 at 7.2% with monthly payments
of $984.19, what is the remaining balance of the loan if the total
payments so far amount to $11,810.24.

The first part of the problem is to figure out how many payments have
been made. That is simply the total payments divided by the month
payment. That becomes "nper" in your FV function expression.

(You would use 20*12 only if you wanted to compute the ending balance
at the end of the loan.)

The second part of the problem is to plug the correct values into the
parameters of the FV function. You got "rate" correct: 7.2%/12.
"nper" is the number of payments actually made so far, computed
above. Ostensibly, "pmt" is -984.19. And "pv" is 125000. Note that
here, "pv" is the name of parameter to the FV function, not the PV
function itself.

Note that "pmt" and "pv" have opposite signs. This is a key concept
for all financial functions: inflow and outflow must have opposite
signs. The choice of which is negative and which is positive is
somewhat arbitrary. Play around with both ways to see the difference.

However, if you plug those numbers in exactly as I have outlined, you
might be surprised to see that the result is (approximately)
122,095.10, not the 122,095.14 that you were led to expect.

Here, frankly, I think the teacher is incorrect. But to get some
insight, use the PMT function to compute the monthly payment. (Note:
For the PMT function, you do want to use 20*12 for "nper" since you
are computing the monthly payment over the entire life of the loan.)
Then replace -984.19 in the FV function with something like -A1, where
"A1" is the cell that has the PMT formula.

You should now get (approximately) 122,095.14.

The issue here is rounding. If you select the PMT cell and change the
format to Scientific with 14 decimal places, you will discover that
the result is not exactly 984.19. Apparently the teacher used this
inexact value in his/her own use of the FV function. This is common
practice, even by loan offiers.

But I say that the teacher is wrong for two reasons. First, if he/she
said that the payment is 984.19, not the result of the PMT function,
which you compute, then his/her FV answer should be based on your
typing that exact number. Second, the payment must be an exact value
in real life; so 984.19 -- the rounded result of the PMT function --
is indeed the correct number to use.

Ergo, I would say that 122,095.10 is indeed the correct remaining
balance.

Hope that helps. Feel to post any follow-up questions.






On Oct 13, 8:09 am, becky wrote:
* * * * Home Loan Schedule * * * * * * * * * * *
* * * * Principal * * * $125,000 * * * *
* * * * Rate * *7.2% * *
* * * * Years * 20 * * *
* * * * Payment $984.19 * * * * per month
Year * *Beginning Balance * * * Ending Balance *Total Paid
*1 * * *$125,000 * * * * * * * * * * * * * * * * * * * * * * * * * * * * 11,810.24

*My professor wants me to use the pv functon for the ending balance on come
up with this figure of 122,095.14. *I have tried everything and can not come
up with this figure. I put 7.2%/12 for rate, *20*12 for nper and 984.19*12
for pmt. *Please help * * * * * * * * *

PS ....

On Oct 13, 1:16*pm, I wrote:
On Oct 13, 8:09*am, becky wrote:
Year Beginning Balance Ending Balance Total Paid
1 $125,000 11,810.24
[....]
Finally, I am not sure you fully understand the problem.
[....]
The first part of the problem is to figure out how many payments have
been made. *That is simply the total payments divided by the month
payment. *That becomes "nper" in your FV function expression.

Well, I guess that was obvious due to the first column of the table
("Year 1"); and it was probably obvious to you as well, evidenced by
your (mistaken) intent to use 984.19*12 for "pmt". I read too quickly
and was fooled by the 3rd-column title ("Ending" instead of
"Remaining" balance).

To reiterate, your mistake, in part, was the use of 20*12 for "nper"
instead of 12, as well as 984.19*12 "pmt" instead of 984.19, as I
explained.

Shame on you.
--
John C


"joeu2004" wrote:

PS ....

On Oct 13, 1:16 pm, I wrote:
On Oct 13, 8:09 am, becky wrote:
Year Beginning Balance Ending Balance Total Paid
1 $125,000 11,810.24
[....]
Finally, I am not sure you fully understand the problem.
[....]
The first part of the problem is to figure out how many payments have
been made. That is simply the total payments divided by the month
payment. That becomes "nper" in your FV function expression.

Well, I guess that was obvious due to the first column of the table
("Year 1"); and it was probably obvious to you as well, evidenced by
your (mistaken) intent to use 984.19*12 for "pmt". I read too quickly
and was fooled by the 3rd-column title ("Ending" instead of
"Remaining" balance).

To reiterate, your mistake, in part, was the use of 20*12 for "nper"
instead of 12, as well as 984.19*12 "pmt" instead of 984.19, as I
explained.