Financial Calc: present cash value of a future amount

[Guest]

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

 

[Roger Govier]

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.

 

[Guest]

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