Discounted Value of money

[Murthy]

I have this following problem.

If a certain amount (P) is receivable after certain period, I would like to
know the present value (V) of that future receipt (P), discounted at a
certain percentage (R). The discounting has to be done on a monthly basis
(like we compound interest on a monthly basis in the reverse case..)

I am not able to find a worksheet function for this. Can someone please
help?

- Murthy

 

[Niek Otten]

Hi Murthy,

PV(rate,#per,[pmt],[fv],[type]) does that.

Look here:

http://office.microsoft.com/en-us/excel/HA011117451033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have this following problem.
|
| If a certain amount (P) is receivable after certain period, I would like to
| know the present value (V) of that future receipt (P), discounted at a
| certain percentage (R). The discounting has to be done on a monthly basis
| (like we compound interest on a monthly basis in the reverse case..)
|
| I am not able to find a worksheet function for this. Can someone please
| help?
|
| - Murthy
|
|

 

[Ron Rosenfeld]

I have this following problem.

If a certain amount (P) is receivable after certain period, I would like to
know the present value (V) of that future receipt (P), discounted at a
certain percentage (R). The discounting has to be done on a monthly basis
(like we compound interest on a monthly basis in the reverse case..)

I am not able to find a worksheet function for this. Can someone please
help?

- Murthy

The PV worksheet function is designed to do just this.

I'm not sure what version of Excel you are using, but had you typed "Present
Value" into the HELP bar, the first choice returned in Excel 2002 was the PV
function.

Just divide your annual interest rate by 12 for the argument, and also express
the number of periods (term) argument in months, in order to account for
monthly discounting.
--ron

 

[Murthy]

Thanks Niek and Ron for your replied.

I will try PV function..

Regards,
Murthy

 

[joeu2004]

If a certain amount (P) is receivable after certain period,
I would like to know the present value (V) of that future
receipt (P), discounted at a certain percentage (R).  The
discounting has to be done on a monthly basis

A concise example might help you get started. Suppose the amount to
be discounted (usually called FV for "future value") is 10,000, and
the annual discount rate is 6% over 5 years. Then present value
(usually called PV) can be computed in one of a number of ways.

=pv(6%/12, 5/12, 0, -10000)

That results in a positive value for PV because I used a negative
value for FV. Many people use the opposite signs: negative for PV,
positive for FV. The choice is arbitrary, and it can decide on your
point of view. But the important point is: be consistent about how
you sign inflows and outflows.

One last point: If 6% is the annual discount rate, there is no common
agreement about how to determine the "sub-annual" rate (monthly, in
this case). Some people simply divide by 12, as above. Other people
compute the compounded monthly rate, for example (two equivalent
ways):

=pv(rate(12,0,-1,1+6%), 5/12, 0, -10000)

or

=pv((1+6%)^(1/12), 5/12, 0, -10000)

The rationale for computing the compound monthly rate is that you want
the annual rate to be 6%. So if you have 100 and you compute the FV
at some monthly rate over 12 months, the result should be 106. The
formula would be:

=fv(r, 12, 0, -100)

If "r" is rate(12,0,-1,1+6%), we get 106, as expected. If "r" is 6%/
12, we get 106.17 (rounded).

 

[joeu2004]

Errata....

=pv(6%/12, 5/12, 0, -10000)
[....]
=pv(rate(12,0,-1,1+6%), 5/12, 0, -10000)
[.....]
=pv((1+6%)^(1/12), 5/12, 0, -10000)

Of course, "5/12" should be 5*12 in all cases.