Problem 30-12
	omplete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. You have been hired as a risk manager for Acorn Savings and Loan. Currently, Acorn's balance sheet is as follows (in millions of dollars):
		Assets		Liabilities
		Cash Reserves	50	Checking and Savings	80
		Auto Loans	100	Certificates of Deposit	100
		Mortgages	150	Long-term Financing	100
				Total Liabilities	280
				Owners' Equity	20
		Total Assets	300	Total Liabilities & Equity	300
	When you analyze the duration of loans, you find that the duration of the auto loans is two years, while the mortgages have a duration of seven years. Both the cash reserves and the checking and savings accounts have a zero duration. The CDs have a duration of two years and the long-term financing has a 10-year duration.
	a.	What is the duration of Acorn's equity?
	b.	Suppose Acorn experiences a rash of mortgage prepayments, reducing the size of the mortgage portfolio from $150 million to $100 million, and increasing cash reserves to $100 million. What is the duration of Acorn's equity now? If interest rates are currently 4% but fall to 3%, estimate the approximate change in the value of Acorn's equity.
	c.	Suppose that after the prepayments in (b), but before a change in interest rates, Acorn considers managing its risk by selling mortgages and/or buying 10-year Treasury STRIPS (zero-coupon bonds). How many should the firm buy or sell to eliminate its current interest rate risk?
		Durations:
		Cash reserves	0
		Auto loans	2
		Mortgages	7
		Checking and savings	0
		CDs	2
		Long-term financing	10
		Weight of cash reserves
		Weight of auto loans
		Weight of mortgages
		Weight of C&S
		Weight of CDs
		Weight of LTF
		Assets/Equity ratio
		Liabilities/Equity ratio
	a.	What is the duration of Acorn's equity?
		Duration of assets
		Duration of liabilities
		Duration of equity
	b.	Suppose Acorn experiences a rash of mortgage prepayments, reducing the size of the mortgage portfolio from $150 million to $100 million, and increasing cash reserves to $100 million. What is the duration of Acorn's equity now? If interest rates are currently 4% but fall to 3%, estimate the approximate change in the value of Acorn's equity.
		New level of mortgages	$100
		Old interest rate	4%
		New interest rate	3%
		Change in interest rate	-1%
		New weight of mortgages
		New asset duration
		New equity duration
		Change in equity value
	c.	Suppose that after the prepayments in (b), but before a change in interest rates, Acorn considers managing its risk by selling mortgages and/or buying 10-year Treasury STRIPS (zero-coupon bonds). How many should the firm buy or sell to eliminate its current interest rate risk?
		Change in equity duration	0
		Change in asset duration	10
		Amount to exchange
		So the firm should
		Number of STRIPS
		Weight of amount to exc.
		New asset duration
		New equity duration
Requirements
	1.	Start Excel - completed.
	2.	In cell D27, by using cell references, calculate the weight of cash reserves(1 pt.).
	3.	In cell D28, by using cell references, calculate the weight of auto loans(1 pt.).
	4.	In cell D29, by using cell references, calculate the weight of mortgages(1 pt.).
	5.	In cell D30, by using cell references, calculate the weight of checking and savings (C&S) in total liabilities(1 pt.).
	6.	In cell D31, by using cell references, calculate the weight of CDs in total liabilities(1 pt.).
	7.	In cell D32, by using cell references, calculate the weight of long-term financing in total liabilities(1 pt.).
	8.	In cell D33, by using cell references, calculate the assets-to-equity ratio(1 pt.).
	9.	In cell D34, by using cell references, calculate the liabilities-to-equity ratio(1 pt.).
	10.	In cell D38, by using cell references, calculate the duration of the total assets(1 pt.).
	11.	In cell D39, by using cell references, calculate the duration of the total liabilities(1 pt.).
	12.	In cell D40, by using cell references, calculate the duration of the equity(1 pt.).
		Note: Refer to the values from Steps 8, 9, 10, and 11 in your calculations.
	13.	In cell D49, by using cell references, calculate the new weight of mortgages(1 pt.).
	14.	In cell D50, by using cell references, calculate the new duration of the total assets(1 pt.).
	15.	In cell D51, by using cell references, calculate the new duration of the equity(1 pt.).
		Note: Refer to the values from Steps 8, 9, 11, and 15 in your calculations.
	16.	To calculate the change in equity value, use the approximate formula: Change in equity value = -Duration * E/(1 + r). In cellD52, by using cell references, input the approximate formula for the change in equity value with the following substitutions: cellD51 as theDuration; cellD47 as the change in interest rate,E; and cellD45 as the discount rate,r.
	17.	In cell D59, by using cell references, calculate the amount to exchange(1 pt.).
	18.	In cell D60, select from the dropdown whether the firm should buy or sell to eliminate its current interest rate risk(1 pt.).
	19.	In cell D61, by using cell references the functionABS, calculate the number of STRIPS as the absolute value of the amount to exchange(1 pt.).
	20.	In cell D62, by using cell references, calculate the weight of the amount to exchange(1 pt.).
	21.	In cell D63, by using cell references, calculate the new asset duration (1 pt.).
	22.	In cell D64, by using cell references, calculate the new duration of the equity(1 pt.).
		Note: Refer to the values from Steps 8, 9, 11, and 21 in your calculations.
	23.	Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.