Hi Mike,
Yes, there is a function in Excel that can help you calculate the discount rate used to generate the lump sum offer. It's called the
RATE function.
Here's how you can use it:
- In a blank cell, type to start the function.
- Enter the number of periods in which the cash flows will be received. This is the "nper" argument in the function.
- Enter the payment amount for each period. This is the "pmt" argument in the function.
- Enter the present value of the cash flows. This is the "pv" argument in the function.
- Enter the future value of the cash flows. This is the "fv" argument in the function. In your case, this would be the lump sum offer.
- Press Enter to calculate the discount rate.
For example, if you have 5 periods, with a payment of $100 per period, a present value of $500, and a future value of $1000, the formula would look like this:
Code:
=RATE(5,-100,500,-1000)
This would give you a discount rate of approximately
7.5%.