# Chapter04 - Excel by authentichero

VIEWS: 817 PAGES: 12

• pg 1
```									Corporate Finance Spreadsheet Templates
Problem 4-10 Problem 4-23 Problem 4-63 Problem 4-75 Problem 4-11 Problem 4-35 Problem 4-67 Problem 4-76 Problem 4-16 Problem 4-59 Problem 4-71

Fundamentals of Corporate Finance by Brealey, Myers, and Marcus -- Fourth Edition Copyright © 2004 Irwin/McGraw-Hill and KMT Software, Inc. (www.kmt.com)

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-10 Objective Calculating number of compounding periods Student Name: Course Name: Student ID: Course Number: How long will it take for \$400 to grow to \$1,000 at the interest rate specified? a. 4% b. 8% c. 16%

Solution
Problem 4-10 Instructions Use the MS Excel NPER functions to solve this problem. Present value Future value \$400 \$1,000 Number of Periods FORMULA FORMULA FORMULA

Rate 4% 8% 16%

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-11 Objective Calculate effective interest rates Student Name: Course Name: Student ID: Course Number: Find the effective annual interest rate for each case: APR Compounding Period 12% 1 month 8% 3 months 10% 6 months

Solution
Problem 4-11 Instructions Enter formulas to calculate the effective annual rate. Compounding Period Effective (in months) Annual Rate 1 FORMULA 3 FORMULA 6 FORMULA

APR 12% 8% 10%

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-16 Objective Calculate compound interest Student Name: Course Name: Student ID: Course Number: Investments in the stock market have increased at an average compound rate of about 8 percent since 1926. a. If you invested \$1,000 in the stock market in 1926, how much would that investment be worth today? b. If your investment in 1926 has grown to \$1 million, how much did you invest in 1926?

Solution
Problem 4-16 Instructions Assuming the year 2002, enter formulas to calculate your answers. Annual average compound rate Number of years Initial investment 8% 76 \$1,000

a. If you invested \$1,000 in the stock market in 1926, how much would that investment be worth today? Value today FORMULA

b. If your investment in 1926 has grown to \$1 million, how much did you invest in 1926? Value in 1926 FORMULA

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-23 Objective Calculate the payments and interest rate of an annuity Student Name: Course Name: Student ID: Course Number: Professor's Annuity Corp. offers a lifetime annuity to retiring professors. For a payment of \$80,000 at age 65, the firm will pay the retiring professor \$600 a month until death. a. If the professor's remaining life expectancy is 20 years, what is the monthly rate on this annuity? What is the effective annual rate? b. If the monthly interest rate is .5 percent, what monthly payment can the firm offer to the retiring professor?

Solution
Problem 4-23 Instructions Use the MS Excel RATE function to solve part a. Use the PMT function to solve part b. a. If the professor's remaining life expectancy is 20 years, what is the monthly rate on this annuity? What is the effective annual rate? Payment at age 65 Life expectancy in months Monthly annuity Monthly rate Effective annual rate \$80,000 240 \$600 FORMULA 0.00%

b. If the monthly interest rate is .5 percent, what monthly payment can the firm offer to the retiring professor? Payment at age 65 Life expectancy in months Monthly rate Monthly annuity \$80,000 240 0.50% FORMULA

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-35 Objective Calculate the effective rate of a mortgage with points Student Name: Course Name: Student ID: Course Number: Home loans typically involve "points," which are fees charged by the lender. Each point charged means that the borrower must pay 1 percent of the loan amount as a fee. For example, if the loan is for \$100,000, and two points are charged, the loan repayment schedule is calculated on a \$100,000 loan, but the net amount the borrower receives is only \$98,000. What is the effective annual interest rate charged on such a loan assuming loan repayment occurs over 360 months? Assume the interest rate is 1 percent per month.

Solution
Problem 4-35 Instructions Use the MS Excel PMT function and the effective rate formula (from the text) to solve this problem. Loan amount \$100,000 Monthly rate 1% Number of periods 360 Points 2% Monthly payment FORMULA Effective annual rate FORMULA

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-59 Objective Calculate periodic deposits to meet a goal Student Name: Course Name: Student ID: Course Number: You believe you will need to have saved \$500,000 by the time you retire in 40 years in order to live comfortably. If the interest rate is 6 percent per year, how much must you save each year to meet your retirement goal?

Solution
Problem 4-59 Instructions Use the MS Excel PMT function to find the annual deposit needed to meet the future goal. Future value (nest egg) Periods (years) Interest rate per year Annual deposit \$500,000 40 6% FORMULA

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-63 Objective Calculate projected retirement funds. Student Name: Course Name: Student ID: Course Number: A couple will retire in 50 years; they plan to spend about \$30,000 a year in retirement, which should last about 25 years. They believe that they can earn 8 percent interest on retirement savings. a. If they make annual payments into your savings plan, how much will they need to save each year? Assume the first payment comes in year one. b. How would your answer to (a) change if the couple also realize that in 20 years, they will need to spend \$60,000 on their child's education?

Solution
Problem 4-63 Instructions Use these assumptions to solve the problem. Hint: Use the Excel time value of money functions. Number of years of retirement Spending per year Rate of return on savings Years till retirement 25 \$30,000 8% 50

a. If they make annual payments into your savings plan, how much will they need to save each year? Assume the first payment comes in year one. Present value of the investment at retirement Savings each year needed FORMULA FORMULA

b. How would your answer to (a) change if the couple also realize that in 20 years, they will need to spend \$60,000 on their child's education? College education amount Years till college education Present value of college education Present value of retirement fund Total Annual annuity amount \$60,000 20 FORMULA FORMULA \$0.00 \$0.00

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-67 Objective Calculate bond values and rates Student Name: Course Name: Student ID: Course Number: You will receive \$100 from a savings bond in 3 years. The nominal interest rate is 8 percent. a. What is the present value of the proceeds from the bond? b. If the inflation rate over the next few years is expected to be 3 percent, what will the real value of the \$100 payoff be in terms of today's dollars? c. What is the real interest rate? d. Show that the real payoff from the bond (from part b) discounted at the real interest rate (from part c) gives the same present value for the bond as you found in part a.

Solution
Problem 4-67 Instructions Use the MS Excel PV function in parts a and b. Facts: Future value of bond Periods (years) Annual interest rate

\$100 3 8%

a. What is the present value of the proceeds from the bond? Present value FORMULA

b. If the inflation rate over the next few years is expected to be 3 percent, what will the real value of the \$100 payoff be in terms of today's dollars? Real value FORMULA

c. What is the real interest rate? Real interest rate FORMULA

d. Show that the real payoff from the bond (from part b) discounted at the real interest rate (from part c) gives the same present value for the bond as you found in part a. Real payoff FORMULA

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-71 Objective Calculate annual inflation Student Name: Course Name: Student ID: Course Number: Inflation in Brazil in 1992 averaged about 23 percent per month. What was the annual inflation rate?

Solution
Problem 4-71 Instructions Enter a formula to calculate the annual inflation rate. Average inflation per month Annual inflation rate 23% FORMULA

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-75 Objective Calculate projected retirement funds. Student Name: Course Name: Student ID: Course Number: What is the value of a perpetuity that pays \$100 every 3 months forever? The discount rate quoted on an APR basis is 6 percent.

Solution
Problem 4-75 Instructions Enter a formula to calculate the value of the perpetuity based on the facts given below. Payment every three months Discount rate (APR) Value of the perpetuity \$100 6% FORMULA

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls

Printed: 6/11/2009

Fundamentals of Corporate Finance
Brealey, Myers, and Marcus 4th Edition
Problem 4-76 Objective Calculate present and future values of lump sums assuming changing interest rates Student Name: Course Name: Student ID: Course Number: If the interest rate this year is 8 percent and the interest rate next year will be 10 percent, what is the future value of \$1 after 2 years? What is the present value of a payment of \$1 to be received in 2 years?

Solution
Problem 4-76 Instructions Use formulas to calculate the future and present values shown below. This year's rate Next year's rate 8% 10% End of this End of next year year FORMULA FORMULA

Future value

Present value of \$1 to be received in 2 years End of this Start of year year 2 Present value FORMULA FORMULA

File: fa9e08a0-d243-447c-9ed8-9c23a50d8fff.xls