Question

Background: Ohio State University sold the right to operate campus parking for $483 million. In exchange for the upfront payment, the purchaser collects all parking fees. The contract calls for parking rates to increase by 5.5% per year for first ten years and 4% thereafter. Answer the following questions assuming the contact lasts for 20 years, the first annual payment for managing the parking facilities is$36,351,926.10and it was received at the end of the first year.

Required:

1. Prepare an Excel spreadsheet detailing the annual payment over the contract life. In order to receive credit for you work, you must attach a copy of your Excel spreadsheet printed with the formulas showing (“Show Formulas” option). When displaying your answers round all numbers to the nearest dollar amount.

          a. Calculate the parking fees for each year of the contract, and the total fees over the contract life.

          b. Use the NPV function to calculate the present value of the fees at 7% and 10%.     

          c. Determine the interest rate the purchaser is charging Ohio State University.

2.Ohio State added the $483 million in proceeds to its endowment to finance student scholarships. If each year Ohio State earns 6% on endowment and pays out 4% for scholarships, calculate the endowment balance at the end of each contract year.

3.Do you see any problems with this arrangement from Ohio State’s perspective?

Answer 1

Dear student, we cannot able to post solution more than four subparts of the question as per our policy.
Part 1	Total fees (Answer 1a)	$                    1,202,955,534
Part 2	NPV @7% (Answer 1b)	$                          92,820,734
Part 3	NPV @10% (Answer 1b)	$                        (38,324,302)
Part 4	Interest rate (Answer 1c)	9.00%

Result View

2019 10 01.xlsx- Microsoft Excel ? X Sign in VIEW FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW DEVELOPER fox D7 B C E G H K L Parking fees Year Year 0 (Investment cost) $ (483,000,000.00) 2 $ Year 1 36,351,926.10 3 $ 38,351,282.04 4 Year 2 $ 5 Year 3 40,460,602.55 $ 6 Year 4 42,685,935.69 7 Year 5 45,033,662.15 8 Year 6 47,510,513.57 9 Year 7 $ 50,123,591.81 $ 10 Year 8 52,880,389.36 11 Year 9 55,788,810.78 12 Year 10 13 Year 11 14 Year 12 $ 58,857,195.37 $ 61,211,483.19 $ 63,659,942.52 15 Year 13 66,206,340.22 $ 16 Year 14 68,854,593.82 17 Year 15 $ 71,608,777.58 $ 18 Year 16 74,473,128.68 $ 77,452,053.83 19 Year 17 20 Year 18 $ 80,550,135.98 21 Year 19 83,772,141.42 22 Year 20 23 Total fees (Answer 1a) 24 NPV @7% (Answer 1b) 25 NPV @10% (Answer 1b) 87,123,027.08 $ 1,202,955,534 $ 92,820,734 (38,324,302) 26 Interest rate (Answer 1c) 9.00% 27 Activate Windows Go to Settingsto activate Winedews. 28. Sheet130 Sheet131 Sheet132 Sheet133 Sheet134 Sheet135 Sheet136 Sheet137 100% READY 12:01 ENG Search the web and Windows 30-10-19 US

Formula View

2019 10 01.xlsx- Microsoft Excel X Sign in DEVELOPER FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW fix D8 A C E Parking fees 1 Year Year 0 (Investment cost) -483*1000*1000 2 Year 1 36351926.1 -B3+(B3 5.5%) =B4+(B4*5.5%) | B5+(B5*5.5%) Year 2 4 5 Year 3 6 Year 4 Year 5 B6+(B6*5.5%) 7 8 Year 6 87+(B7*5.5%) 9 Year 7 B8+(B8*5.5%) 10 Year 8 B9+(B9*5.5%) B10+(B10*5.5%) 11 Year 9 12 Year 10 13 Year 11 14 Year 12 B11+(B11*5.5%) B12+(B12 4%) |B13+(B13*4%) B14+(B14*4%) | B15+(B15*4%) -B16+ (B16 4% ) | B17+(B17*4%) |=B18+(B18 4 %) B19+(B19*4%) 15 Year 13 16 Year 14 17 Year 15 18 Year 16 19 Year 17 20 Year 18 -820+(B20*4%) 21 Year 19 22 Year 20 23 Total fees (Answer 1a) 24 NPV @7% (Answer 1b) 25 NPV @10% (Answer 1b) | =B21+(B21*4%) -SUM(B3:B22) =NPV(7%,B3:B22)-483000000 =NPV(10%,B3:B22)-483000000 26 Interest rate (Answer 1c) IRR(B2:B22) 27 Activate Windows Go to Settings to activate Windows. 28. Sheet130 Sheet131 Sheet132 Sheet133 Sheet134 Sheet135 Sheet136 Sheet137 100% READY + 12:02 ENG Search the web and Windows 30-10-19 US