5) Today is t = 0. The interest rate is 8%. The inflation rate is 1%. You will make your first deposit of $14,000 at t = 10. Each year thereafter you will deposit 5% more money into the account, until t = 16. Each year after that you will deposit 7% more money into the account, until your last deposit at t = 20. At t = 26 you will make withdrawals that will allow you to consume the same amount of goods that $4,000 can buy today. For each year thereafter up to t = 45 you will withdraw enough money to allow you to consume 3% more goods than the previous year. You will then let the money sit in the bank until t = 50. a) How much money will be left in the bank account at t = 50? Use the nominal approach. b) How much money will be left in the bank account at t = 50? Use the real approach. c) Download "Assignment 1 Table.xlsx" from the assignments folder online. Fill in the table as explained on the spreadsheet. Make sure that you don't type 0.08 in any of your formulas. Instead, when you need to use the risk free rate, reference the cell $G$1. This is necessary for part d. Also, you will find it easier to compute relevant nominal growth rates in the indicated cells, and reference those cells in your table. Compute the nominal growth rates using the appropriate formulas directly within those cells so that you avoid any rounding errors. Make sure to print your paper so that it fits on one sheet. You only need to hand in this one page for part c of this question. (The goal of this question is twofold: first, to allow you to visualize what is happening in part a; and second to familiarize yourself with Excel. Excel is widely used in finance and in business in general. You would be wise to become very good at Excel. Note, however, that you will not be tested on Excel in your midterm or final exam.) d) Now we want to make use of the Excel Solver function to answer the following question, which cannot be solved analytically. Suppose the risk free rate were not 8%. For what value of the risk free rate would the above deposits and withdrawals lead to a final balance of exactly $4M at t = 50? To answer this question, copy your table from partc to another page (do this by right-clicking on the tab at the bottom and then click on "move or copy", and select "copy"). Now on this new page, do not change any values or formulas. Instead, just go to Data, then Solver*. Go to options, and set "constraint precision" to 0.000000001. Click OK. Now tell solver to set cell G56 to = 4000000, by changing cell G1. Print this second version of your table, again making sure it fits on 1 page. (*Note: in order to use solver, you may have to go to "file, then options, then add-ins, then analysis toolpack, then click go, then select solver and click OK.) Fill in the red cells in th All values should be in real growth rate of withdrawal nominal growth rate of withdrawals - Period Deposits PV/Deposit) Withdrawals PV/Withdrawal) Account balance before deposit withdrawal Account balance after deposit withdrawal this final balance should be your answer to and b PV all deposits)-> (sum of column C) Note that PV all deposits) should equal PV(all withdrawals) + PV/final balance) 5) Today is t = 0. The interest rate is 8%. The inflation rate is 1%. You will make your first deposit of $14,000 at t = 10. Each year thereafter you will deposit 5% more money into the account, until t = 16. Each year after that you will deposit 7% more money into the account, until your last deposit at t = 20. At t = 26 you will make withdrawals that will allow you to consume the same amount of goods that $4,000 can buy today. For each year thereafter up to t = 45 you will withdraw enough money to allow you to consume 3% more goods than the previous year. You will then let the money sit in the bank until t = 50. a) How much money will be left in the bank account at t = 50? Use the nominal approach. b) How much money will be left in the bank account at t = 50? Use the real approach. c) Download "Assignment 1 Table.xlsx" from the assignments folder online. Fill in the table as explained on the spreadsheet. Make sure that you don't type 0.08 in any of your formulas. Instead, when you need to use the risk free rate, reference the cell $G$1. This is necessary for part d. Also, you will find it easier to compute relevant nominal growth rates in the indicated cells, and reference those cells in your table. Compute the nominal growth rates using the appropriate formulas directly within those cells so that you avoid any rounding errors. Make sure to print your paper so that it fits on one sheet. You only need to hand in this one page for part c of this question. (The goal of this question is twofold: first, to allow you to visualize what is happening in part a; and second to familiarize yourself with Excel. Excel is widely used in finance and in business in general. You would be wise to become very good at Excel. Note, however, that you will not be tested on Excel in your midterm or final exam.) d) Now we want to make use of the Excel Solver function to answer the following question, which cannot be solved analytically. Suppose the risk free rate were not 8%. For what value of the risk free rate would the above deposits and withdrawals lead to a final balance of exactly $4M at t = 50? To answer this question, copy your table from partc to another page (do this by right-clicking on the tab at the bottom and then click on "move or copy", and select "copy"). Now on this new page, do not change any values or formulas. Instead, just go to Data, then Solver*. Go to options, and set "constraint precision" to 0.000000001. Click OK. Now tell solver to set cell G56 to = 4000000, by changing cell G1. Print this second version of your table, again making sure it fits on 1 page. (*Note: in order to use solver, you may have to go to "file, then options, then add-ins, then analysis toolpack, then click go, then select solver and click OK.) Fill in the red cells in th All values should be in real growth rate of withdrawal nominal growth rate of withdrawals - Period Deposits PV/Deposit) Withdrawals PV/Withdrawal) Account balance before deposit withdrawal Account balance after deposit withdrawal this final balance should be your answer to and b PV all deposits)-> (sum of column C) Note that PV all deposits) should equal PV(all withdrawals) + PV/final balance)