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

Hi Murthy,

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

Look he

http://office.microsoft.com/en-us/ex...117451033.aspx

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Murthy" wrote in message ...
|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
|
|

On Thu, 14 Feb 2008 13:14:26 +0530, "Murthy" wrote:

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

Thanks Niek and Ron for your replied.

I will try PV function..

Regards,
Murthy


"Ron Rosenfeld" wrote in message
...
On Thu, 14 Feb 2008 13:14:26 +0530, "Murthy" wrote:

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

On Feb 13, 11:44*pm, "Murthy" wrote:
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).

Errata....

On Feb 14, 8:45*am, I wrote:
=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.