UPIN18 2020 In order to receive credit for your answers, use Excel Financial Functions or Algebraic Time Value of Money Equations to answer these questions in your spreadsheet. See General Guide- lines for Excel Group Assignments' in 'Excel Group Assignments' folder on CourseWeb. Please Type the Names of the Group Members at the Top of the First Page of your Workbook. Part I: Future Value Construct a table and a graph showing the relationship between Interest Rates, Time, and Future Value by showing how $5,000 would grow each successive year over a 30 year period at different interest rates. Use $5,000 for your present value and calculate the future value of this $5,000 each year over the 30 year period at 0%, 2%, 4%, 8%, 10%, 14%, 18%, and 22% compounded annually. Future Value should be the y-axis for your graph and years (or time) should be your x-axis and you should end up with a line for each interest rate on your graph. Please insert your graph (chart) under your table of future values. Part II: Present Value Construct a table and a graph showing the relationship between Interest Rates, Time, and Present Value by showing how $5,000 pushed a year further into the future over a 30 year period would be discounted at different interest rates. Use $5,000 for your future value and calculate the present value of this $5,000 each year over the 30 year period at 0%, 2%, 4%, 8%, 10%, 14%, 18%, and 22% compounded annually. Present Value should be the y-axis for your graph and years (or time) should be your x-axis and you should end up with a line for each interest rate on your graph. Please insert your graph (chart) under your table of future values. Part III: College Education You and your spouse just had a baby. Ecstatic with the outstanding education you received at the University of Pittsburgh, you want to send your baby to college in 18 years and be able to BUSFIN 1030: TVM Assignment pay for your baby's college education. You need to estimate cost of each year of college (you're only paying for the 4 years necessary to complete a bachelor's degree) for when your baby starts college 18 years from today. Also, you want to estimate how much you need to save in order to be able to pay these future college costs. Here are some assumptions to help you with your analysis: You want your baby to enjoy the same quality education at Pitt that you received. The current annual cost estimate to cover all student expenses today is $33,000. You anticipate that college costs will rise 3.8% at an annual basis until baby starts college in 18 years. After the start of college, you expect college costs to go up only 1.5% from year 1 to 2,2% from year 2 to 3, and 2.5% from year 3 to 4 since the tuition part of baby's college costs is fixed for the course of your baby's undergraduate program. Your baby will need annual college money at the beginning of each year. This means 4 withdrawals from baby's college fund at the beginning of her freshman year (18 years from today), sophomore year, junior year, and senior year. Any money saved in baby's college fund will earn an after-tax return of 5.3% annually, The balance of baby's college fund should be zero after the withdrawal at the beginning of baby's senior year of college (21 years from today). Answer the following questions to help finalize your savings goals for baby's college fund. I What will be the expected cost of each year of baby's college? II One way you can fund your baby's future college costs (from the previous question) is by making a single deposit today in the college fund that pays a 5.3% annual rate. How large of a deposit do you need to make today? III After determining the single deposit that you would need to make from the last question, you get sticker shock and decide that is too much to handle all at once today. To help you get started, the grandparents decide to deposit $20,000 today in baby's college fund. You decide to cover the rest of baby's future college costs by making 18 annual equal deposits at 5.3% into baby's college fund starting a year from today (the last deposit will be made when your baby starts college). How large does this annual deposit need to be in addition to the grandparents' $20,000 initial gift to baby's college fund? Part IV: Retirement Planning You realize the wisdom of starting early at age 22 in saving for your retirement and plan on making 43 equal end of year annual deposits in an IRA account in hopes of having at least $1,000,000 once you retire at age 65 immediately after your last deposit into the IRA account), but you think it would be best to have $1,500,000 at age 65 to retire. Answer the following questions based on this information. I What rate compounded annually would you need to earn in order to have $1,500,000 upon retirement if your annual deposit is $4,000? 2/4 BUSFIN 1030: TVM Assignment II What annual deposit would you need in order to accumulate $1,500,000 upon retirement if your IRA earns 7% compounded annually? III Now you decide you might need more than $1,500,000 in your IRA once you turn 65 given the uncertainty of Social Security. How much money would you have in your IRA at age 65 after 43 end of the year deposits of $5,000 invested at an expected annual return of 8%? IV Assume you carry out the plan described in the last question (#III) and expect to continue to earn an 8% annual return on your IRA account after retirement. Once you reach retirement at age 65, you decide you will live exactly 23 more years. At age 65, you want two things from your retirement fund: $250,000 to leave to your grandkids upon your death in 23 years and a 23-year annuity-due (23 equal annual withdrawals beginning immediately upon retirement). How large can your annual retirement annuity be while still leaving $250,000 to your grandkids upon your death at age 88 if your IRA account continues to earn 8% annually? V In looking back at your answer #IV, you wonder if this retirement annuity will be enough to live on and you worry about potential loss of purchasing power after retirement. Let?s forget about leaving money to the grandkids, they never visit anyway, and make some different retirement income assumptions. Assume you want your first retirement withdrawal, taken immediately upon retirement, to be $145,000 at age 65. Also, let?s assume you want each of the remaining (22) withdrawals to increase by 3% annually. Assume your retirement account will still earn an 8% expected annual return. a How much will you need in your retirement account at age 65 to fund this desired retire- ment income stream? b How much will you have to deposit annually at the end of each year into your account over the next 43 years to fund this desired retirement stream? Part V: Retirement Savings You realize you are entering the last third of your career and you are considering retirement in 10 years. You are in a self-managed defined contribution pension plan and through automatic payroll deduction and employer matching - both based on mandated percentages of your salary - $1,350 per month is currently deposited into your pension plan. Due to the lack of recent raises at your company, you don't plan on these monthly contributions increasing much, if any, over the next 10 years. You currently have $545,000 in your pension plan account and you are somewhat concerned if this along with your mandated future $1,350 monthly deposits will adequately fund your retire- ment in 10 years. You are considering supplementing your pension plan by having automatic additional monthly deposits deducted into a 403c retirement plan you have a monthly deposit amount in mind, but wants additional help in trying to figure out if this amount will be ade- quate. Given your current pension plan portfolio investment mix, you estimate a nominal annual expected return of 7.8% which translates to a 0.65% monthly expected return. 3/4 BUSFIN 1030: TVM Assignment 1 You are considering having $900 per month which is about 10% of your monthly gross (pre- tax) salary deducted and deposited into the 403c for the next 10 years. This is in addition to the $545,000 already in your pension plan today and the estimated $1,350 monthly deposits into your current pension plan. What is the expected total value of your retirement ac- counts after making end of the month deposits of $2,250 for 10 years on top of your current retirement savings of $545,000 at your expected monthly return? II You estimate you will live for 25 years after your planned retirement in 10 years and you want a monthly retirement annuity with the withdrawals at the end of each month once you retire. What is your expected monthly retirement income using your answer from #1 and assuming you will continue to earn your expected monthly return after retirement? III Upon hearing the amount of this monthly retirement annuity, you are happy with this figure because it's higher than your current pre-tax income. However, you wonder if your expected monthly investment return is too optimistic and you want to change it to a 6% nominal annual rate, or 0.5% per month after retirement, but a 7.2% nominal annual rate, or 0.6% before. Also, you want a monthly income of $10,000 once you retire (for 25 years). How much extra above your estimated mandated $1,350 monthly pension plan amount would you need to deposit monthly into the 403c plan over the next 10 years to fund this retirement income goal? income goal? Part VI: Car Loan You are looking to buy a 2018 Ford Focus Titanium Hatchback with sunroof and leather seats at a price of $26,000. Being a college student, you have cash to pay taxes, title, license and fees but your parents offer to give you 10% of the price, $2,600, as a down payment and you need to finance the remainder of $23,400. You smartly researched your finance options and got pre- approval at a 3.6% APR with monthly compounding for 5 years on your own so you are not at the mercy of depending on financing from the car dealership. Ford is offering 0% APR financing or $3,250 cash back which would be used as an additional down payment that reduces the amount that you would need to finance. You would use your 3.6% APR pre-approved financing if you elect the $3,250 cash back option. Answer the following questions. I What would be your monthly car loan payment under the Ford's 0% APR financing offer (assume a 60-month loan term)? II What would be your monthly car loan payment under the Ford's $3,250 cash back offer and your 3.6% APR pre-approved financing? III At what APR would you be indifferent between the two offers? In other words, at what APR would you have the same monthly payment (assuming a 60-month loan term) for the $3,250 cash back offer as you would with the 0% APR financing offer? IV Assume you go with the offer in question #II. Construct an amortization schedule for the loan for all 60 monthly payments. What is your loan balance after 36 months? V Now assume you were willing to make a monthly car loan payment of $450. Re-do your amortization schedule from question #IV with this monthly payment. How long will it take to pay off the loan with the $450 monthly payment? 4/4 UPIN18 2020 In order to receive credit for your answers, use Excel Financial Functions or Algebraic Time Value of Money Equations to answer these questions in your spreadsheet. See General Guide- lines for Excel Group Assignments' in 'Excel Group Assignments' folder on CourseWeb. Please Type the Names of the Group Members at the Top of the First Page of your Workbook. Part I: Future Value Construct a table and a graph showing the relationship between Interest Rates, Time, and Future Value by showing how $5,000 would grow each successive year over a 30 year period at different interest rates. Use $5,000 for your present value and calculate the future value of this $5,000 each year over the 30 year period at 0%, 2%, 4%, 8%, 10%, 14%, 18%, and 22% compounded annually. Future Value should be the y-axis for your graph and years (or time) should be your x-axis and you should end up with a line for each interest rate on your graph. Please insert your graph (chart) under your table of future values. Part II: Present Value Construct a table and a graph showing the relationship between Interest Rates, Time, and Present Value by showing how $5,000 pushed a year further into the future over a 30 year period would be discounted at different interest rates. Use $5,000 for your future value and calculate the present value of this $5,000 each year over the 30 year period at 0%, 2%, 4%, 8%, 10%, 14%, 18%, and 22% compounded annually. Present Value should be the y-axis for your graph and years (or time) should be your x-axis and you should end up with a line for each interest rate on your graph. Please insert your graph (chart) under your table of future values. Part III: College Education You and your spouse just had a baby. Ecstatic with the outstanding education you received at the University of Pittsburgh, you want to send your baby to college in 18 years and be able to BUSFIN 1030: TVM Assignment pay for your baby's college education. You need to estimate cost of each year of college (you're only paying for the 4 years necessary to complete a bachelor's degree) for when your baby starts college 18 years from today. Also, you want to estimate how much you need to save in order to be able to pay these future college costs. Here are some assumptions to help you with your analysis: You want your baby to enjoy the same quality education at Pitt that you received. The current annual cost estimate to cover all student expenses today is $33,000. You anticipate that college costs will rise 3.8% at an annual basis until baby starts college in 18 years. After the start of college, you expect college costs to go up only 1.5% from year 1 to 2,2% from year 2 to 3, and 2.5% from year 3 to 4 since the tuition part of baby's college costs is fixed for the course of your baby's undergraduate program. Your baby will need annual college money at the beginning of each year. This means 4 withdrawals from baby's college fund at the beginning of her freshman year (18 years from today), sophomore year, junior year, and senior year. Any money saved in baby's college fund will earn an after-tax return of 5.3% annually, The balance of baby's college fund should be zero after the withdrawal at the beginning of baby's senior year of college (21 years from today). Answer the following questions to help finalize your savings goals for baby's college fund. I What will be the expected cost of each year of baby's college? II One way you can fund your baby's future college costs (from the previous question) is by making a single deposit today in the college fund that pays a 5.3% annual rate. How large of a deposit do you need to make today? III After determining the single deposit that you would need to make from the last question, you get sticker shock and decide that is too much to handle all at once today. To help you get started, the grandparents decide to deposit $20,000 today in baby's college fund. You decide to cover the rest of baby's future college costs by making 18 annual equal deposits at 5.3% into baby's college fund starting a year from today (the last deposit will be made when your baby starts college). How large does this annual deposit need to be in addition to the grandparents' $20,000 initial gift to baby's college fund? Part IV: Retirement Planning You realize the wisdom of starting early at age 22 in saving for your retirement and plan on making 43 equal end of year annual deposits in an IRA account in hopes of having at least $1,000,000 once you retire at age 65 immediately after your last deposit into the IRA account), but you think it would be best to have $1,500,000 at age 65 to retire. Answer the following questions based on this information. I What rate compounded annually would you need to earn in order to have $1,500,000 upon retirement if your annual deposit is $4,000? 2/4 BUSFIN 1030: TVM Assignment II What annual deposit would you need in order to accumulate $1,500,000 upon retirement if your IRA earns 7% compounded annually? III Now you decide you might need more than $1,500,000 in your IRA once you turn 65 given the uncertainty of Social Security. How much money would you have in your IRA at age 65 after 43 end of the year deposits of $5,000 invested at an expected annual return of 8%? IV Assume you carry out the plan described in the last question (#III) and expect to continue to earn an 8% annual return on your IRA account after retirement. Once you reach retirement at age 65, you decide you will live exactly 23 more years. At age 65, you want two things from your retirement fund: $250,000 to leave to your grandkids upon your death in 23 years and a 23-year annuity-due (23 equal annual withdrawals beginning immediately upon retirement). How large can your annual retirement annuity be while still leaving $250,000 to your grandkids upon your death at age 88 if your IRA account continues to earn 8% annually? V In looking back at your answer #IV, you wonder if this retirement annuity will be enough to live on and you worry about potential loss of purchasing power after retirement. Let?s forget about leaving money to the grandkids, they never visit anyway, and make some different retirement income assumptions. Assume you want your first retirement withdrawal, taken immediately upon retirement, to be $145,000 at age 65. Also, let?s assume you want each of the remaining (22) withdrawals to increase by 3% annually. Assume your retirement account will still earn an 8% expected annual return. a How much will you need in your retirement account at age 65 to fund this desired retire- ment income stream? b How much will you have to deposit annually at the end of each year into your account over the next 43 years to fund this desired retirement stream? Part V: Retirement Savings You realize you are entering the last third of your career and you are considering retirement in 10 years. You are in a self-managed defined contribution pension plan and through automatic payroll deduction and employer matching - both based on mandated percentages of your salary - $1,350 per month is currently deposited into your pension plan. Due to the lack of recent raises at your company, you don't plan on these monthly contributions increasing much, if any, over the next 10 years. You currently have $545,000 in your pension plan account and you are somewhat concerned if this along with your mandated future $1,350 monthly deposits will adequately fund your retire- ment in 10 years. You are considering supplementing your pension plan by having automatic additional monthly deposits deducted into a 403c retirement plan you have a monthly deposit amount in mind, but wants additional help in trying to figure out if this amount will be ade- quate. Given your current pension plan portfolio investment mix, you estimate a nominal annual expected return of 7.8% which translates to a 0.65% monthly expected return. 3/4 BUSFIN 1030: TVM Assignment 1 You are considering having $900 per month which is about 10% of your monthly gross (pre- tax) salary deducted and deposited into the 403c for the next 10 years. This is in addition to the $545,000 already in your pension plan today and the estimated $1,350 monthly deposits into your current pension plan. What is the expected total value of your retirement ac- counts after making end of the month deposits of $2,250 for 10 years on top of your current retirement savings of $545,000 at your expected monthly return? II You estimate you will live for 25 years after your planned retirement in 10 years and you want a monthly retirement annuity with the withdrawals at the end of each month once you retire. What is your expected monthly retirement income using your answer from #1 and assuming you will continue to earn your expected monthly return after retirement? III Upon hearing the amount of this monthly retirement annuity, you are happy with this figure because it's higher than your current pre-tax income. However, you wonder if your expected monthly investment return is too optimistic and you want to change it to a 6% nominal annual rate, or 0.5% per month after retirement, but a 7.2% nominal annual rate, or 0.6% before. Also, you want a monthly income of $10,000 once you retire (for 25 years). How much extra above your estimated mandated $1,350 monthly pension plan amount would you need to deposit monthly into the 403c plan over the next 10 years to fund this retirement income goal? income goal? Part VI: Car Loan You are looking to buy a 2018 Ford Focus Titanium Hatchback with sunroof and leather seats at a price of $26,000. Being a college student, you have cash to pay taxes, title, license and fees but your parents offer to give you 10% of the price, $2,600, as a down payment and you need to finance the remainder of $23,400. You smartly researched your finance options and got pre- approval at a 3.6% APR with monthly compounding for 5 years on your own so you are not at the mercy of depending on financing from the car dealership. Ford is offering 0% APR financing or $3,250 cash back which would be used as an additional down payment that reduces the amount that you would need to finance. You would use your 3.6% APR pre-approved financing if you elect the $3,250 cash back option. Answer the following questions. I What would be your monthly car loan payment under the Ford's 0% APR financing offer (assume a 60-month loan term)? II What would be your monthly car loan payment under the Ford's $3,250 cash back offer and your 3.6% APR pre-approved financing? III At what APR would you be indifferent between the two offers? In other words, at what APR would you have the same monthly payment (assuming a 60-month loan term) for the $3,250 cash back offer as you would with the 0% APR financing offer? IV Assume you go with the offer in question #II. Construct an amortization schedule for the loan for all 60 monthly payments. What is your loan balance after 36 months? V Now assume you were willing to make a monthly car loan payment of $450. Re-do your amortization schedule from question #IV with this monthly payment. How long will it take to pay off the loan with the $450 monthly payment? 4/4