To JoeU2004

[dindigul]

Hello
I read that using PMT you calculate your instalment/EMI on particular amount
of Loan.
GoalSeek on the other hand finds out the max loan you can get if you have x
amount to spare per month.
So I spared 5000 per month at 9% interest how much it accumulates as
GoalSeek, I was told does exactly opposite of working with PMT. I am not
against using FV, but while learning Excel, I found goalseek not working
correctly. with 5k per month = 60K in a year. in 5 yrs it is 300K, plus
interest. How can that be around 240k? is the question
Thanks

 

[Niek Otten]

Please stay in your original thread.

The Present Value (PV) of regular payments is indeed 240K.
If you pay a lumpsum of 240K now, that is worth regular payments of 5000 per
month, 60 times, with an annual interest rate of 14.5%.
Maybe you should read Help once more to understand the meaning of Present
Value.

 

[JoeU2004]

For future reference, it is better to keep related postings in the original
thread (see "original thread" below) in order to retain the context. And it
is better not to address the subject to a single person so that you do not
exclude others who might have valuable inputs.

I read that using PMT you calculate your
instalment/EMI on particular amount of Loan.
GoalSeek on the other hand finds out the max
loan you can get if you have x amount to spare
per month.

Yes; and PV (not FV, by the way) will give you the same result as Goal Seek,
based on the inquiry in your original thread.

Similary, Goal Seek, as well as PMT, can be used to compute the monthly
payment if you know the interest rate, number of payments, and loan amount.

The point is: Goal Seek and PMT or PV are not mutually exclusive; they are
simply different ways to compute the same thing. But you need to do an
apples-to-apples comparison: Goal Seek and PMT, or Goal Seek and PV.

In this particular case, PV can be used instead of Goal Seek. But you seem
to want to learn how to use Goal Seek. That's fine.

So I spared 5000 per month at 9% interest how much it accumulates as
GoalSeek, I was told does exactly opposite of working with PMT.

"Accumulates"? That sounds like a savings problem, not a loan problem.
That might be the root cause of your confusion and why Goal Seek (and PV) do
not meet your expectations.

Again, it is not that the Goal Seek computation is "the opposite" PMT. It
is the fact you want to compute FV (so it seems now), not PMT.

I am not against using FV, but while learning Excel, I found
goalseek not working correctly. with 5k per month = 60K
in a year. in 5 yrs it is 300K, plus interest. How can
that be around 240k? is the question

The mistake is yours, not Goal Seek's.

If the interest rate is 9% (you used 14.5% previously) and you want to
determine the savings at 5000 per month over 5 years, the correct formula
is:

=FV(9%/12, 5*12, -5000, 0)

Likewise, for Goal Seek, if B1 is =PMT(9%/12,5*12,0,-A1) and A1 will be the
accumulated savings, set "Set cell" to B1, "To value" to 5000, and "By
changing cell" to A1.

Both will give the same result, namely about 377,120.68 formatted to 2
decimal places.

Does that meet your expectations?

Notes:

1. Your mistake before might have omitting the PV (0) from the PMT
parameters.

2. Pay close attention to the sign of cash flows. Inflows and outflows
should have opposite signs. But what you consider "inflow" or "outflow" is
arbitrary, depending on your point of view (saver v. bank; borrower v.
lender). As you can see, I tend to change my point of view so that
financial fuction, e.g. FV and PMT, yield positive numbers. Sorry if that's
confusing.


----- original message ----

Hello
I read that using PMT you calculate your instalment/EMI on particular
amount of Loan.
GoalSeek on the other hand finds out the max loan you can get if you have
x amount to spare per month.
So I spared 5000 per month at 9% interest how much it accumulates as
GoalSeek, I was told does exactly opposite of working with PMT. I am not
against using FV, but while learning Excel, I found goalseek not working
correctly. with 5k per month = 60K in a year. in 5 yrs it is 300K, plus
interest. How can that be around 240k? is the question
Thanks

----- original thread -----

Using =PMT formula, I input B1=pmt(14.5%/12,60,A1) and
in GoalSeek, I set cell B1 to 5000 by changing cell A1
and I get 212,510.21

That is the correct result, assuming either you are misrepresenting the
signs of some things.

When A1 is 212,510.21, =PMT(14.5%/12,60,-A1) does indeed return about
5000.00 formatted to 2 decimal places. Note that I used -A1, not A1.

Alteratively, with PMT(...,A1), A1 should be -212,510.21; or if A1 is
212,510.21, PMT() should result in -5000.

whereas simply seen 5000 in one year would be 60K and
in 5 years it would be 300K.

Off-hand, I don't know that means. But I confess that I am not giving it
much thought since Goal Seek does indeed produce the correct result.

What wrong am I doing?

I'm biting my tongue

Seriously, if you can explain what you mean by the "whereas" statement
above, perhaps we can help you understand the Goal Seek result and/or
properly structure the Goal Seek problem to meet your expectations.

PS: You do not need Goal Seek for the problem as I understand it. Simply
use:

=PV(14.5%/12,60,-B1)

where B1 is 5000.


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