In: Finance
How would you calculate this with an excel formula on excel:
•What is the future value of the following cash flows if the discount rate is 8%? What is the future value if the discount rate is 5%? (this is asking you take the future value of the cash flows. Please find the future value at year 4. ))
There are 2 ways to do this: Since the cash flow in not given i am taking a seriies of cash flow for 4 years:
Period | 1 | 2 | 3 | 4 |
Cash Flow | 5 | 5 | 5 | 5 |
Discount Rate | 8% | |||
Future Value | 22.53 | =FV(8%,4,5) |
In the above method you use the direct formula for future value in excel which is FV(rate, nper, pmt)
where rate = 8%, nper =4 (since 4 years of cash flow) and pmt= 5 which is basciallyt he cash flow each year. For 5%, you just need to change the rate in the formula to 5%
Now for 2nd method:
Period | Cash Flow | Future Value of each indiviudal Cash flow | |
1 | 5 | 6.30 | = 5*(1+8%)^(4-1) |
2 | 5 | 5.83 | = 5*(1+8%)^(4-2) |
3 | 5 | 5.40 | = 5*(1+8%)^(4-3) |
4 | 5 | 5.00 | = 5*(1+8%)^(4-4) |
22.53 | = sum of each future value and hence the total future value |
Here, you find the future aclue of each individual cash flow by using the formula FVn = 5*(1+Rate)^(4-n)
So FV1= future valuje of cashflow in period 1 = 5*(1+8%)^(4-1)
Finally add all of the years future values to get the future value of all the cash flows which comes to 22.53, same as the previous method
As you can see, the first method uses an excel formula and cannot be used if the cash flows are not constant over years whereas the 2nd method is more general