Lump sum calculation

[Joe]

Does any one know what function (PMT - PPMT- PV- FV) I
have to use the get my results?
I want to have 18 years from now the amount of $70,000
If the interest rate will always be 12%
I want to make only one payment new.
Can any one help me find the function?

I couldn't do it by my self, I need your help.
Thanks

Joe

 

[Norman Harker]

Hi Joe!

Try:
=PV(12%,18,0,70000,0)
Returns: -9102.77130820508
The negative reflects a payment out of 9102 in return for a payment in
of 70000
You could negate the function or negate the 70000 if you want a
positive sign but in general terms life is less confusing if you
follow a strict money flow signing convention.

Proof is by:
=-D5*(1+12%)^18
Returns 70000

Alternative formula approach is:
=70000*(1+12%)^-18
Returns: 9102.77130820508

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jerry W. Lewis]

See Help for FV.

Jerry

 

[Joe]

Thanks Norman

Great!!
This works perfectly if I use = PV(0.12,18,0,70000,0)
However, I'm not able to use the Percent sign "%" like = PV
(12%,18,0,70000,0)
Am I missing something?

Thanks again
Joe

 

[Norman Harker]

Hi Joe!

=PV(12%,18,0,70000,0)
Works OK for me.

Try a copy and paste from the formula above directly into the formula
bar and see if you still have problems.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Joe]

Thanks Norman

I think I have the function, I just realized that I need
the option to indicate the amount I already have put in.
Let say I already have $5, 000 and want to have in 18
years from now the amount of $70,000
I tried playing with the PV function as well with others
but no luck.

Can you help me?

Thanks
Joe

 

[Norman Harker]

Hi Joe!

I'm probably misinterpreting what you want but is it:

=PV(12%,18,0,70000,0)+5000

The PV is as before and you needed to deposit 9102.77

But you already have 5000

In essence you're finding how much more you need now than the $5000
you have if you want to accumulate 70000 in 18 years at 12%.

Or is it the annual payment that you want to calculate:

=PMT(12%,18,-5000,70000,0)
Returns: -565.925243714188
i.e an annual in arrears payment of $565.93 has to be made.

That final argument is an assumption that you are making payments at
the end of each year.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Joe]

Thanks Norman again
Let me clarify my question,
I already have $5,000, and want to know how much I have to
add new (1 Payment only) in order to have $70,000
However, you have to consider that I will also earn
interest on the $5,000
Hope to hear from you
Thanks again
Joe

 

[Norman Harker]

Hi Joe!

That's the following:

=PV(12%,18,0,70000,0)+5000
Returns: -4102.77130820508

Proof (especially that you get interest on the 5000):

=FV(12%,18,0,-(4102.771308+5000),0)
Returns: 70000.00
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Joe]

Norman, Thanks again

You are 100% right.
One more question,
The function works fine, however, how do I take out the
negative (-)?
Thanks

Joe

 

[Norman Harker]

Hi Joe!

If the sign of the answer is not what you want then negate the formula
as a whole:

=-(PV(12%,18,0,70000,0)+5000)
Returns: 4102.77130820508

The alternative is to change the signs of the inputs:

=PV(12%,18,0,-70000,0)-5000
Returns: 4102.77130820508

But that tends to be more confusing.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Joe]

Thanks
Thanks
Thanks
Joe

 

[Norman Harker]

Hi Joe!

Thanks for Thanks

For "internal" use within Excel, I'd recommend getting used to the
sign convention Excel uses regarding cash flows rather than negating
the formula.

For reports and summary tables it's a matter of taste and often I
think they look and interpret better with all positives and clear
headings.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.