2006 the State of Indiana in the USA sold a 75-year concession to operate and maintain the East-West Toll Road. Before doing so, it commissioned a consulting report that estimated the value of the concession. Table 1 shows the forecasted cash flow from the toll road.
Table 1 Forecasted cash flows (million $)
| 2006-2015 | 2016-2025 | 2026-2035 | 2036-2045 | 2046-2055 | 2056-2065 | 2066-2075 | 2076-2081 | |
| Revenues | 1,746.6 | 2,604.6 | 3,908.2 | 5,453.6 | 7,588.6 | 10,749.0 | 14,656.2 | 11,797.9 |
| Expenditures | ||||||||
| General operating | 468.8 | 771.0 | 1,267.8 | 2,084.9 | 3,428.6 | 5,638.3 | 9,272.1 | 8,227.9 |
| Repairs and renovations | 577.8 | 705.2 | 839.4 | 1,011.2 | 1,231.2 | 1,512.7 | 1,873.1 | 1,337.8 |
| Total expenditures | 1,046.6 | 1,476.2 | 2,107.2 | 3,096.1 | 4,659.8 | 7,151.0 | 11,145.2 | 9,565.7 |
| Revenues over expenditures | 700.0 | 1,128.4 | 1,801.0 | 2,357.5 | 2,928.8 | 3,598.0 | 3,511.0 | 2,232.2 |
The above discount rate was chosen by the consultant of the report on the assumption that it was the interest rate the State of Indiana paid on its bonds. Do you agree with that assumption? Explain your answer
The present value of the concession using a discount rate of 6%. Cash flows are reported in Table 1 for each ten-year block up until 2066–2075 with the last block as five years (2076–2081).
| Discount rate should be equal to the cost of capital | |||||||||||
| Cost of capital for State of Indiana is the interest it pays on its Bonds | |||||||||||
| Hence,the discount rate chosen is reasonabe and we agree with the rate | |||||||||||
| Block of 10 Year 2006-2015 | ($million) | ||||||||||
| Pmt1 | Net cashflow | 700.00 | |||||||||
| Rate1 | Discount rate | 6% | 0.06 | ||||||||
| Nper1 | Number of years of net cashflow | 10 | |||||||||
| PV1 | Present Value at beginning of 2006 | $5,152.06 | (Using PV function of excelwith Rate=6%,Nper=10, Pmt=-700.00) | ||||||||
| Block of 10 Year 2016-2025 | ($million) | ||||||||||
| Pmt2 | Net cashflow | 1,128.40 | |||||||||
| Rate2 | Discount rate | 6% | |||||||||
| Nper2 | Number of years of net cashflow | 10 | |||||||||
| PV2 | Present Value at beginning of 2016 | 8,305.12 | (Using PV function of excelwith Rate=6%,Nper=10, Pmt=-1128.40) | ||||||||
| A | Present Value at thebeginning of 2006 | 4,637.54 | (8305.12/(1.06^10) | ||||||||
| Block of 10 Year 2026-2035 | ($million) | ||||||||||
| Pmt3 | Net cashflow | 1,801.00 | |||||||||
| Rate3 | Discount rate | 6% | |||||||||
| Nper3 | Number of years of net cashflow | 10 | |||||||||
| PV3 | Present Value at beginning of 2026 | 13,255.52 | (Using PV function of excelwith Rate=6%,Nper=10, Pmt=-1801.0) | ||||||||
| B | Present Value at thebeginning of 2006 | 4,133.13 | (13255.52/(1.06^20) | ||||||||
| Block of 10 Year 2036-2045 | ($million) | ||||||||||
| Pmt4 | Net cashflow | 2,357.50 | |||||||||
| Rate4 | Discount rate | 6% | |||||||||
| Nper4 | Number of years of net cashflow | 10 | |||||||||
| PV4 | Present Value at beginning of 2036 | 17,351.41 | (Using PV function of excelwith Rate=6%,Nper=10, Pmt=-2357.5) | ||||||||
| C | Present Value at thebeginning of 2006 | 3,021.06 | (17351.41/(1.06^30) | ||||||||
| Block of 10 Year 2046-2055 | ($million) | ||||||||||
| Pmt5 | Net cashflow | 2,928.80 | |||||||||
| Rate5 | Discount rate | 6% | |||||||||
| Nper5 | Number of years of net cashflow | 10 | |||||||||
| PV5 | Present Value at beginning of 2046 | 21,556.22 | (Using PV function of excelwith Rate=6%,Nper=10, Pmt=-2928.8) | ||||||||
| D | Present Value at thebeginning of 2006 | 2,095.74 | (21556.22/(1.06^40) | ||||||||
| Block of 10 Year 2056-2065 | ($million) | ||||||||||
| Pmt6 | Net cashflow | 3,598.00 | |||||||||
| Rate6 | Discount rate | 6% | |||||||||
| Nper6 | Number of years of net cashflow | 10 | |||||||||
| PV6 | Present Value at beginning of 2056 | 26,481.59 | (Using PV function of excelwith Rate=6%,Nper=10, Pmt=-3598.0) | ||||||||
| E | Present Value at thebeginning of 2006 | 1,437.64 | (26481.59/(1.06^50) | ||||||||
| Block of 10 Year 2066-2075 | ($million) | ||||||||||
| Pmt7 | Net cashflow | 3,511.00 | |||||||||
| Rate7 | Discount rate | 6% | |||||||||
| Nper7 | Number of years of net cashflow | 10 | |||||||||
| PV7 | Present Value at beginning of 2066 | 25,841.27 | (Using PV function of excelwith Rate=6%,Nper=10, Pmt=-3511.0) | ||||||||
| F | Present Value at the beginning of 2006 | 783.36 | (25841.27/(1.06^60) | ||||||||
| Block of 5 Year 2076-2080 | ($million) | ||||||||||
| Pmt8 | Net cashflow | 2,232.20 | |||||||||
| Rate8 | Discount rate | 6% | |||||||||
| Nper8 | Number of years of net cashflow | 5 | |||||||||
| PV8 | Present Value at beginning of 2076 | 9,402.84 | (Using PV function of excelwith Rate=6%,Nper=5, Pmt=-2232.2) | ||||||||
| G | Present Value at the beginning of 2006 | 159.17 | (9402.84/(1.06^70) | ||||||||
| PV=PV1+A+B+C+D+E+F+G | PRESENT VALUE OF THE CONCESSION | 21,419.70 | |||||||||
2006 the State of Indiana in the USA sold a 75-year concession to operate and maintain...
Database Design Problem You have been hired to review the accuracy of the books of Northeast Seasonal Jobs International (NSJI), a job broker. NSJI matches employers whose business is seasonal (like ski resorts) with people looking for part time positions at such places. Employers are located in the Northeast (New England), but recently expanded to include parts of Canada. NSJI maintains all of this information in a flat file spreadsheet (see attached layout of the spreadsheet structure). Required: 1) From...
Python Pandas, Series and DataFrame Question (NO Loops, No If Statements, No List Comprehensions) The file bank.csv contains data about bank customers. The last column ('Personal Loan') indicates whether or not the customer was approved for a personal loan or not. Write a function named loan_by_zip that accepts 3 parameters: a file name, a minimum number of records, and a percentage approval rate. The function should return a DataFrame of those zip codes for which we meet the minimum number...