Present Value Equation in Excel. Please help?

Question

I have an offer of getting 9 payments of $500,000. The payments would begin with the first payment today of $500,000 and then continue to be made every 6 months.

I would like to know the present value of the total $4.5MM if it earns 10% interest

Is this the equation =pv(.1/2,9,500000,0,1)?

What if I wanted the interest compounded monthly? How would I do that?

Answer 1

For your first question, the easiest way to calculate Present Value of your cash stream is to ignore the first payment (today) for a second. This is because the first payment is worth 500000 today, and after calculating the PV of the income stream, all you would need to do is add 500K to it.

The other variables are as follows:

Pmt = 500000

N = 8 (Since we removed one of the payments)

r = 5% (10% is the annual rate, with monthly compounding the rate will be half that = %5)

So you would use =PV(5%,8,500000), which will give you roughly 3.2 million. The number will be negative because of the cash outflow. You just have to take away the negative sign. At this point in time, dont forget to add back the 500K that we put aside in the beginning. So, the final number would be $ 3731606.38.

For monthly compounding, what matter is when you receive cash. If you receive cash on a monthly basis, then you would just divide the annual rate by 12 to get the monthly rate (10%/12 = 0.833%) and also change the number of periods from semi-annual to monthly. Apply the same formula, and you should be good to go.