Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
rpalarea
 
Posts: n/a
Default Financial Calc: present cash value of a future amount

I thnk part of my problem is that I'm not sure what type of financial
valuation I'm trying to find, but here is the basic scope:

1. My customer wants to buyout my contract with them early.
2. The contract is 36 months, with 7 months left (29 payments have been made)
3. Payments are made mothly, but vary in amount. The amount due each month
might be roughly estimated through historical data (averaging and weighted
averages), but the final amount isn't available for calculation until the
actual month end and could vary by +/- 10% of any estimate.
4. The value I'm trying to calculate would be an amount that could be paid
today, based on the historical data available, discounted for the value of
having the payment today instead of 7 months from now.

I looked at PV and NPV functions. NPV seems to be the most appropriate, but
I don't understand how NPV takes into account the number of months left in
the term. I also don't know what discount rate/interest rate to use.

This is a very straighforward calculation that someone in the finance world
probably does a few times each day or week.

Thanks in advance!

Rich
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default Financial Calc: present cash value of a future amount

Hi Rich

I don't think the length of the contract comes into it.
Basically, you are asking what is the value today, for receiving all 7
payments left instead of waiting to receive them at the end of each of
the remaining 7 months.
Let's assume that the average value per month is 2000.
Let's assume interest rate is 6% or 6%/12 to make it monthly
Then
=PV(6%/12,7,2000)
returns a value of -13724.15.
So it would be worth investing 13724.15 today, to gain 2000 per month
for 7 months at an interest rate of 6% per anum, so that is the value to
you for receiving the payments today instead of waiting.
Substitute whatever monthly figure you want, and interest rate that you
think is appropriate.

--
Regards

Roger Govier


"rpalarea" wrote in message
...
I thnk part of my problem is that I'm not sure what type of financial
valuation I'm trying to find, but here is the basic scope:

1. My customer wants to buyout my contract with them early.
2. The contract is 36 months, with 7 months left (29 payments have
been made)
3. Payments are made mothly, but vary in amount. The amount due each
month
might be roughly estimated through historical data (averaging and
weighted
averages), but the final amount isn't available for calculation until
the
actual month end and could vary by +/- 10% of any estimate.
4. The value I'm trying to calculate would be an amount that could be
paid
today, based on the historical data available, discounted for the
value of
having the payment today instead of 7 months from now.

I looked at PV and NPV functions. NPV seems to be the most
appropriate, but
I don't understand how NPV takes into account the number of months
left in
the term. I also don't know what discount rate/interest rate to use.

This is a very straighforward calculation that someone in the finance
world
probably does a few times each day or week.

Thanks in advance!

Rich



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
rpalarea
 
Posts: n/a
Default Financial Calc: present cash value of a future amount

Roger - worked like a charm. Thanks so much.
Rich

"Roger Govier" wrote:

Hi Rich

I don't think the length of the contract comes into it.
Basically, you are asking what is the value today, for receiving all 7
payments left instead of waiting to receive them at the end of each of
the remaining 7 months.
Let's assume that the average value per month is 2000.
Let's assume interest rate is 6% or 6%/12 to make it monthly
Then
=PV(6%/12,7,2000)
returns a value of -13724.15.
So it would be worth investing 13724.15 today, to gain 2000 per month
for 7 months at an interest rate of 6% per anum, so that is the value to
you for receiving the payments today instead of waiting.
Substitute whatever monthly figure you want, and interest rate that you
think is appropriate.

--
Regards

Roger Govier


"rpalarea" wrote in message
...
I thnk part of my problem is that I'm not sure what type of financial
valuation I'm trying to find, but here is the basic scope:

1. My customer wants to buyout my contract with them early.
2. The contract is 36 months, with 7 months left (29 payments have
been made)
3. Payments are made mothly, but vary in amount. The amount due each
month
might be roughly estimated through historical data (averaging and
weighted
averages), but the final amount isn't available for calculation until
the
actual month end and could vary by +/- 10% of any estimate.
4. The value I'm trying to calculate would be an amount that could be
paid
today, based on the historical data available, discounted for the
value of
having the payment today instead of 7 months from now.

I looked at PV and NPV functions. NPV seems to be the most
appropriate, but
I don't understand how NPV takes into account the number of months
left in
the term. I also don't know what discount rate/interest rate to use.

This is a very straighforward calculation that someone in the finance
world
probably does a few times each day or week.

Thanks in advance!

Rich




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting dates for a the present month but not future months BrianInCalifornia Excel Worksheet Functions 3 December 7th 05 03:00 AM
Future Value function with differenct cash flows Joe V Excel Discussion (Misc queries) 1 September 3rd 05 06:06 AM
NPV Calc appears incorrect with a large # of negative cash flows MikeW Excel Worksheet Functions 1 September 1st 05 03:31 PM
Can you limit the amount of times a calc is performed on certain cells n_davies Excel Discussion (Misc queries) 1 January 24th 05 11:43 PM
calculating the future value of a present value se Excel Worksheet Functions 2 December 8th 04 04:42 PM


All times are GMT +1. The time now is 10:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"