Financial Valuation: Calculating the Internal Rate of Return (Math Problem Sample)

Financial Valuation – Homework 1
Guideline for homework assignment
It is an essential skill to solve financial problems with Excel. This course use Excel extensively in the assignments.
* Most of the class examples and assignments are explained and implemented using excel modelling.
* For the excel analysis, the example spreadsheet explains all the calculations.
* In the assignment, the excel template will guide you through the homework and project.
* You should organize and submit your work with excel worksheet.
Q1-1. Calculating present values
Calculate the present value of each of the following cash flow streams. Use a discount rate of 10%.
Hint: study the excel function, PV(rate, nper, pmt, fv, type=0).
* $500 received at the end of five years
* $500 received annually for each of the next five years
* $500 received at the end of fifty years.
* $500 received annually for 100 years
Q1-2. Calculating the internal rate of return
Singular Construction is evaluating whether to build a new distribution facility. The proposed investment will cost Singular $4 million to construct and provide cash savings of $500,000 per year over the next ten years.
Hint: study the excel function IRR.
* What internal rate of return does the investment offer?
* If Singular were to invest another $200,000 in the facility at the end of five years, it would extend the life of the project by four years, during which time it would continue receiving cash savings of $500,000. What is the internal rate of return for this investment?
Hint: You need to replicate the case a (in green) and apply the calculations to case b (in blue).
Q1-3. Boca Corp’s outstanding bonds have a $1000 par value, a 9% annual coupon rate, semiannual payments, 8 years to maturity, and 8.5% yield to maturity. What is the bond’s price?
Hint:
The yield to maturity (APR) is quoted annual base, while the periodical discount rate for calculate is based on the coupon frequency.
Q1-4. A stock is expected to pay a dividend of $0.50 at the end of the year (D1 = 0.50).
* Suppose the dividend will continue to grow at a constant rate of 7% a year. If its required rate of return is 12%, what is the stock’s expected price 4 year from now? What is the price now?
* Suppose the dividend will continue to grow at a constant rate of 7% a year for the next four years, and then remain at 3% after year 4. If it required rate of return is 12%, what is the price 4 year from now? What is the price now?
Q1-5. Introductory Project Valuation
The CT Computers Corporation is considering whether to begin offering customers the option to have their old personal computers (PCs) recycled when they purchase new systems. The recycling system would require CT Computers to invest $600,000 in the grinders and magnets used in the recycling process. The company estimates that for each system it recycles, it would generate $1.50 in incremental revenues from the sale of scrap metal and plastics. The machinery has a five-year useful life and will be depreciated using straight-line depreciation toward a zero sa