Please see the attached excel sheet there are 10 tabs that have questions to be answered.
#1 Assignment 6.4 Exercises Problem 1: Time Value of Money - Single Sums5 Points a) What is the present value of $10,000 that is going to be received in 5 years, if the appropriate interest rate is 5 percent? b) What is the present value of $10,000 that is going to be received in 8 years, if the appropriate interest rate is 5 percent? c) What is the present value of $10,000 that is going to be received in 5 years, if the appropriate interest rate is 10 percent? d) What will be the value in 10 years of $10,000 invested today, if the appropriate interest rate is 5 percent? e) At an interest rate of 10 percent, how many years will it take for a $10,000 investment to double in value? Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs, and be sure to answer both parts of the question. Input / Output area: Present ValueInterest RateNumber of PeriodsFuture Value a)$ - b)$ - c)$ - d)$ - e)ERROR:#DIV/0! This is the Student Template, provided in the assignment instructions October 2019 #2 Assignment 6.4 Exercises Problem 2: Time Value of Money - Single Sums5 Points a) If $5,000 is invested today at an interest rate of 8 percent, what will its value be in 10 years? b) If $20,000 is needed to make a future purchase, but only $10,000 is available today, how long will it take for the money to grow if the interest rate is 10%? c) A student needs $25,000 to get their MBA, but has $15,000 they could set aside today. If the interest rate is 8%, how long would they have to wait? d) At an interest rate of 5 percent, how much money would have to be invested today to reach a goal of $25,000 in 10 years? e) At an interest rate of 12 percent, how much would an investment today of $15,000 be worth in 5 years? Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers. This is the Student Template, provided in the assignment instructions October 2019 #3 Assignment 6.4 Exercises Problem 3: Rate of Return Calculations5 Points a) An investment of only $2,000 today returns $150,000 in 40 years. What is the rate of return on this investment? b) An investment costs $100,000 today but promises to return $1,000,000 in 20 years. What is the promised rate of return? c) An investment promises to double your money in only 10 years. What is the implied rate of return on this investment? d) What return are you earning if you pay $500,000 for a stream of $50,000 annual payments for 20 years? e) An investment costs $100,000. It will return $10,000 the first year, but then the return will increase by $10,000 every year until the fifth year, when it finally reaches $50,000. What is the rate of return on this investment? Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs, and be sure to answer both parts of the question. Input / Output area: Present ValueFuture ValueNumber of PeriodsRate of Return a)ERROR:#NUM! b)ERROR:#NUM! c)ERROR:#NUM! Present ValuePaymentsNumber of Periods d)ERROR:#NUM! Year:012345Rate of Return e)Cash FlowERROR:#NUM! This is the Student Template, provided in the assignment instructions October 2019 #4 Assignment 6.4 Exercises Problem 4: Rate of Return Calculations5 Points a) An investor deposits $150,000 into a retirement account. They feel they need $1,200,000 when they retire in 20 years. What return must they earn to make their goal? b) An investment promises an annual income of $35,000 for 15 years. The investment costs $350,000 upfront. What is the implied rate of return on this investment? c) A company is considering purchasing an $85,000 piece of equipment that will save them $20,000 per year for five years. In the final year, they anticipate being able to sell (salvage) the old equipment for $15,000. What is the rate of return on this purchase? Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers. This is the Student Template, provided in the assignment instructions October 2019 #5 Assignment 6.4 Exercises Problem 5: Perpetuities5 Points a) An aspiring entreprenuer is considering purchasing a miniature donut-making machine for $2,300. After accounting for production costs, the entrepreneur estimates they can generate $1,000 of free cash flow annually with the machine, which will effectively last forever. What is their rate of return on this investment? b) An investor will permit you purchase their British Consol bond. The bond pays 35 British pounds annually, and these payments will continue forever. If you require an 8 percent annual return to make the investment, what should you pay? Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs, and be sure to answer both parts of the question. Input / Output area: Present ValueCash FlowRate of Return a)ERROR:#DIV/0! Present ValueCash FlowRate of Return b)ERROR:#DIV/0! This is the Student Template, provided in the assignment instructions October 2019 #6 Assignment 6.4 Exercises Problem 6: Perpetuities5 Points a) You are offered an opportunity to purchase a share of Preferred Stock that promises to pay an annual return of 9%. If the purchase price is $175, how much does the stock pay in dividends annually? b) You require the investments in your retirement portfolio to return a minimum of 8%. Your insurance agent offers to sell you an investment that promises to return $10,000 annually forever. How much is this investment worth to you as part of your retirement plan? Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers. This is the Student Template, provided in the assignment instructions October 2019 #7 Assignment 6.4 Exercises Problem 7: Annuities5 Points a) A local car dealership leases a high-end automobile for $6,000 annually. At the end of the five-year lease, the car can be sold for $25,000. If the dealership's expected return is 13 percent, how much is this lease worth? b) A small company borrows $600,000 from their local bank at a rate of 8%. The loan payments are made annually and last for 10 years. What is the company's annual payment? c) A recent graduate of IWU's MBA program has decided to reward themselves by purchasing a new Ford Mustang at a cost of $32,750. The interest rate on a 6-year loan is 9%. What will be the montly payment? Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs, and be sure to answer both parts of the question. Input / Output area: Present ValuePaymentsRate of ReturnNumber of PeriodsFuture Value a)$ - Present ValueAnnual PaymentRate of ReturnNumber of PeriodsFuture Value b)ERROR:#NUM! Present ValueMonthly PaymentAnnual RateNumber of YearsFuture Value c)ERROR:#NUM! This is the Student Template, provided in the assignment instructions October 2019 #8 Assignment 6.4 Exercises Problem 8: Annuities5 Points a) An elderly couple is considering purchasing an annuity to provide guaranteed income for their retirement. Their personal financial advisor offers to sell them an annuity that will pay $63,000 per year to cover their living expenses for the next 15 years. If they only expect a 5% return on their money, how much should they be willing to pay for this annuity investment? b) An astute IWU MBA graduate is considering refinancing their student loans. A special rate of 3.46% is available. The graduate would make monthly payments for the next 10 years. If the student owes $25,000, what will be their payment amount? Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers. This is the Student Template, provided in the assignment instructions October 2019 #9 Assignment 6.4 Exercises Problem 9: Capital Budgeting5 Points The expected free cash flow from an investment is shown in the table below: . YearCash Flow 0$ (650,000.00) 1$ 50,000.00 2$ 175,000.00 3$ 250,000.00 4$ 250,000.00 5$ 200,000.00 The company's executives require a 12% return on all investments. a) What is the Payback Period of this investment? b) What is the Net Present Value of this investment? c) What is the Benefit-Cost Ratio (Profitability Index) of this investment? d) What is the Internal Rate of Return (IRR) of this investment? e) Based on the company's criterion, should it pursue this investment? Use the Template Provided Below to Create Your Solution - Pay close attention to the formulas and formatting of the inputs, and be sure to answer both parts of the question. Input area: Cash Flows: Initial Cost Year 1 Cash Inflow Year 2 Cash Inflow Year 3 Cash Inflow Year 4 Cash Inflow Year 5 Cash Inflow Required Return (%) Output area: . a)Payback Period (years)ERROR:#DIV/0! b)Net Present Value$ - c)Benefit-Cost RatioERROR:#DIV/0! d)Internal Rate of ReturnERROR:#NUM! e)Should be pursued?No! This is the Student Template, provided in the assignment instructions October 2019 #10 Assignment 6.4 Exercises Problem 10: Capital Budgeting15 Points The expected free cash flow from an investment is shown in the table below: .. YearCash Flow 1$ 75,000.00 2$ 200,000.00 3$ 275,000.00 4$ 350,000.00 5$ 375,000.00 6$ 250,000.00 7$ 175,000.00 8$ 100,000.00 The cost of this investment is $1 million. The company's Board of Directors requires an 11% return on all investments. a) What is the Payback Period of this investment? b) What is the Net Present Value of this investment? c) What is the Benefit-Cost Ratio (Profitability Index) of this investment? d) What is the Internal Rate of Return (IRR) of this investment? e) Based on the company's criterion, should it pursue this investment? Create your Original Solution Below - Be sure to show all calculations and clearly indicate answers. This is the Student Template, provided in the assignment instructions October 2019 Example 1 Workshop Six Practice Exercises Example 1: The PV Function a) What is the present value of $11,000 that is going to be received in 11 years, if the appropriate interest rate is 11 percent? b) What is the present value of $10,000 received every year for 8 years, if the going rate of interest is 12%? c) An new piece of equipment returns $7,000 in savings every year for 15 years. It will then be sold (salvaged) for $15,000. What is the value of this equipment today, if the company's required return is 9%? d) An entrepreneur borrows money to purchase a franchise. Their monthly payment on the loan is $3500, and it lasts 5 years. The loan's annual interest rate (APR) is 8.5%. How much did they borrow? e) A young couple is interested in purchasing a home. They determine the largest mortgage payment they can afford is $1,500 per month. The current mortgage rate is 3.625%. What is the maximum amount they can borrow with a standard 30-year mortgage. What if they instead choose to do a 15-year mortgage? Use the space below to create your solution. If you get stuck, or when you are ready to check your answer, go to the next worksheet tab for the solution. This is the student Practice Problem file, provided in the assignment instructions October 2019 Ex # 1 Solution Workshop Six Practice Exercises Example 1: The PV Function a) What is the present value of $11,000 that is going to be received in 11 years, if the appropriate interest rate is 11 percent? b) What is the present value of $10,000 received every year for 8 years, if the going rate of interest is 12%? c) An new piece of equipment returns $7,000 in savings every year for 15 years. It will then be sold (salvaged) for $15,000. What is the value of this equipment today, if the company's required return is 9%? d) An entrepreneur borrows money to purchase a franchise. Their monthly payment on the loan is $3500, and it lasts 5 years. The loan's annual interest rate (APR) is 8.5%. How much did they borrow? e) A young couple is interested in purchasing a home. They determine the largest mortgage payment they can afford is $1,500 per month. The current mortgage rate is 3.625%. What is the maximum amount they can borrow with a standard 30-year mortgage. What if they instead choose to do a 15-year mortgage? Check below for a detailed solution to this problem. Input / Output area:This example is designed to give you practice using Excel's PV function. This function can be used to calculate the Present Value of a single lump sum, of a level series of payments (an annuity), or a combination of the two. Present ValueInterest RateNumber of PeriodsPaymentsFuture ValueExamine the formulas in column C closely… a)$ 3,490.1211%11$ -$ 11,000.00Note there is only one lump sum future value in this case, so the annuity payment "pmt" is set to zero. b)$ 49,676.4012%8$ 10,000.00$ -In this case there is a regular, repeating payment (an annuity), but no final future value