Question

In: Finance

How would you calculate this with an excel formula on excel: •What is the future value...

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. ))

Solutions

Expert Solution

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


Related Solutions

How do you calculate Payback Period on Excel by using FAME function? What is the formula?
How do you calculate Payback Period on Excel by using FAME function? What is the formula?
A) Write a macro function in excel to calculate the Future value for a series of...
A) Write a macro function in excel to calculate the Future value for a series of cash flows. B. Write a macro function to estimate the cost of equity using the: (i) Capital Asset Pricing Model (CAPM) Ri = rf + Ba (rm-rf) where: rf = the rate of return on risk-free securities (typically Treasuries) Ba = the beta of the investment in question rm = the market's overall expected rate of return (ii) Fama-French Model Ri = rf +...
What is an annuity and how do you calculate the future value of an ordinary annuity...
What is an annuity and how do you calculate the future value of an ordinary annuity and an annuity due?
How do you calculate Profit Present Value in excel?
How do you calculate Profit Present Value in excel?
a. Write down the formula used to calculate the Present Value (PV) of a future Cash...
a. Write down the formula used to calculate the Present Value (PV) of a future Cash Flow (CF)   for ‘n’ years. Using this formula, explain why the price of a coupon bond and the yield to maturity are negatively related.   b. If there is an increase in interest rates, which would you rather be holding, long-term bonds or short-term bonds? Why? Which type of bond has the greater interest-rate risk?
What is the formula to calculate how much a savings account would be worth if the...
What is the formula to calculate how much a savings account would be worth if the initial balance is $1,000 with monthly deposits of $75 for 10 years at 4.3% annual interest compounded monthly? What is the formula result? You want a savings account to grow from $1,000 to $5,000 within two years. Assume the bank provides a 3.2% annual interest rate compounded monthly. What is the formula to calculate how much you must deposit each month to meet your...
What is Mirr? how do you find it using excel without using excel formula? Below is...
What is Mirr? how do you find it using excel without using excel formula? Below is cash flow. I found IRR is 15.59% and required return is 12% CF Y0 Y1 Y2 Y3 Y4 Y5 Y6 Capital spen $ (30,000,000.00) $ 15,062,400.00 Opp cost $   (5,000,000.00) NWC $   (1,120,000.00) $      (150,000.00) $      (170,000.00) $      140,000.00 $   1,300,000.00 OCF $     9,015,600.00 $ 11,392,500.00 $ 12,193,200.00 $ 10,435,800.00 $ (2,640,000.00) $   (3,960,000.00) CF $ (36,120,000.00) $     8,865,600.00 $ 11,222,500.00 $ 12,333,200.00 $...
I'm struggling with this problem. How would you calculate the P-Value and what would it tell...
I'm struggling with this problem. How would you calculate the P-Value and what would it tell you? In 2008, 17% of homes in North County had been contacted by a member of the Solar Solutions sales team. Recently a sample of 220 homes across several North County neighborhoods indicated that 21% had been contacted. At alpha = 0.05 can it be supported that the true proportion of Solar Solutions contacted homes has actually increased?
Describe in detail how to calculate the future value and the present value of a series...
Describe in detail how to calculate the future value and the present value of a series of cash flows.
using this, approximate formula, without the bond prices, how can we calculate future discount rates / forward rates in excel and I am stuck in (DR 11/20) in excel
1Y0.03832Y0.0515Y0.055910Y0.069120Y0.074630Y0.0744using this, approximate formula, without the bond prices, how can we calculate future discount rates / forward rates in excel and I am stuck in (DR 11/20) in excel(1+ DR1) (1+ DR2) = (1+YTM2)2
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT