if i can please have the answers in the excel form where they go in the pictures, that be terrific and appreciated.

BTW. . .A. Merge Center - $ - % 848 Conditional Format as Cell Formatting Table Styles Styles Number : Clipboard Alignment FontS A43 - X a. How much money will Jenna have at her retirement? DEFGH M Part 2 Integrative Data Case 3. This case draws on material from Chapters 3-7. Adam has just graduated, and has a good job at a decent starting salary. He hopes to purchase his first new car. The car that 6 Adam is considering costs $47,000. The dealer has given him three payment options: 1 Zero percent financing. Make a $2.800 down payment from his savings and finance the remainder with a 0% APR loan for 7 60 months. Adam has more than enough cash for the down payment, thanks to generous graduation gifts. 82 Rebate with no money down. Receive a $5,000 rebate from the car dealer and finance the rest with a standard 60 month 9 loan, with an 4 95% APR. He likes this option, as he could think of many other uses for the $2,800 of his saving 10 3. Pay cash. Get the $5,000 rebate and pay the rest with cash. While Adam doesn't have balance of the car cost in hand, he 11 wants to evaluate this option. His parents always paid cash when they bought a family car: Adam wonders if this really was a good 12 idea 14 Adam's fellow graduate, Jenna, has been trying to decide how much of her new salary she could save for retirement. Jenna is considering 15 putting $3,000 of her annual savings in a stock fund. She just turned 22 and has a long way to go until retirement at age 60, and she considers 16 this risk level reasonable. The fund she is looking at has earned an average of 6.10% over the past 15 years and could be expected to 17 continue earning this amount, on average. While she has no current retirement savings, eight years ago Jenna's grandparents gave her a new Instruction Adam - Questions 1-4 Jenna - Questions 5-9 Type here to search Formatting Font Number Argent Cells Styles X a. How much money will jenna have at her retirement? No2 16 this risk level reasonable. The fund she is looking at has earned an average of 6.10% over the past 15 years and could be expected to 17 continue earning this amount, on averape. While she has no current retirement savings, eight years ago Jenna's grandparents gave her a new 18 30-year U.S. Treasury bond with a $20,000 face value with 4.25% semiannual coupons Jenna wants to know her retirement income if she both (1) sells her Treasury bond at its current market value and invests the proceeds in the stock fund and (2) 20 saves an additional $3,000 at the end of each year in the stock fund from now until she retires. Once she retires, Jenna wants those savings to last until she is 94 23 Case Questions 24 1. What are the cash flows associated with each of Adam's three cat financing options? 25 2. Suppose that, similar to his parents, Adam had plenty of cash in the bank so that he could easily afford to pay cash for the car without running into debt 26 now or in the foreseeable future. If his cash cans interest at a 5.4% APR (based on monthly compounding) at the bank, what would be his best purchase option for 27 the cat? 20 3. In fact, Adam doesn't have sufficient cash to cover all his debts including his substantial student loans. The loans have a 10% APR, and any money spent 23 on the car could not be used to pay down the loans What is the best option for Adam now? ( Note that having an extra S1 today saves Adam roughly $1.10 30 next year because he can pay down the student loans. So, 10% is Adam's time value of money in this case) 31 4 Suppose instead Adun has a lot of credit card debt, with an 189. APR, and he doubts he will pay off this debt completely before he pays off the car. What 12 is Adam's best option ou 30 5. Suppose Jenna's Treasury bond has a coupon interest rate of 4.25%, paid semia lly, while current Treasury bonds with the same maturity date have a to mshion a nd ADR camisam ifche hatch the band's 16th on what the be of lana's Instruction Adam Questions 1-4 Jenna Cuestions 5-9 Type here to search o Mew W. General Times New Roma - 14 - AA BIU. . . A Wrap fert Merge & Center : + $ -% 988 HEMEN Conditional Format as Cell Insert Delete Form Formatting Table Styles Styles Cells Clipboard Alignment Font A43 1 X Sea. How much money will Jenna have at her retirement? N O C 314. Suppose instead Adam has a lot of credit card debt, with an 18% APR, and be doubts he will pay off this debt completely before he pays off the car. What 32 is Adam's best option now? 33 5. Suppose Jenna's Treasury bond has a coupon interest rate of 4.25%, paid semiannually, while current Treasury bonds with the same maturity date have a 34 yield to maturity of 2 90 % (expressed as an APR with semiannual compounding). If she has just received the bond's 16th coupon, what is the value of Jenna's 35 Treasury Bond today? 36 6. Suppose Jenna sells the bond, reinvests the proceeds, and then saves as she planned. If indeed, Jenna earns a 6.10% annual return on her savings, how 37 much could she withdraw each year in retirement? (Assume she begins withdrawing the money from the account in equal amounts at the end of each year once her 38 retirement begins.) 7 Jenna expects her salary to grow regularly. While there are no guarantees, she believes an increase of 2.50% a year is reasonable. She plans to save $3,000 the first year, and then increase the amount she saves by the amount of her annual salary increase. Unfortunately, prices will also grow due to inflation Suppose Jenna assumes there will be 1.90% inflation every year. In retirement, she will need to increase her withdrawals each year to keep up with inflation a How much money will Jenna have at her retirement? b. How much can she withdraw at the end of the first year of her retirement in today's dollars? Hint Value Jenna's Retirement Fund at Retirement Age = FV of Treasury Bond-FV of Jenna's Savings 8. Should Jenna sell her Treasury bond and invest the proceeds in the stock fund? Give at least one reason for and against this plan. 9. At the last minute, Jerna considers investing in Coca-Cola stock at a price of $53.48 per share instead. The stock just paid an annual dividend of 43 $1.60 and she expects the dividend to grow at 4.00% annually. If the next dividend is due in one year, what expected return is Coca-Cola stock offering? Instruction Adam questions Ben Questions Type here to search o i me W X Data Review View Help Search AutoSum - Ay Home Insert Page Layout Formulas Calibri -12 - BIU.aA E- 2. Wrap Test Merge Center - Conditional Formatas Cell Formatting Table Styles Insert Delete Format - $ . % 9 28 Sort & Filters Clear Editing Styles Cem Alignment addition to other neces a ration contained $47.000 NOTE Use this information to solve the data can Adam's car purchase Car cost Loan Termin months Option 1 Down Payment Option 2 and 3 lebate Option 2 Loan rate Deposit interest Rates as Glven in Text 222 Down Payment Instruction Adam Questions 1-4 Questions 5-9 Type here to search BIU - - A.A. Merge & Center - $ -% 688 Conditional Formatas Cell Formatting" Table Styles Styles Insert Delete For - cells Alignment Number Chipboard F36 : * fer Cash M onth tashow for Month 1 to Question 2 (12 ) PV of Car Financing at Down Payment N arinancinta Instruction Adam Questions 1.4 Jenna Questions 5.9 Type here to search Data Review View Help Search File AX Home insert Page Layout Formulas Calibri 12 AA BIU.2.0.A. Wrap Text Autosum - 47 Sort rt Delete Format To Clean Filter 0 Find & Select - 1 deas C e Merge Center - $ . % 9 .28 Condition Formatting Table Styles Editing 03 - X Step 2: Once you know how much Jenna saves by retirement, this will become Jenna's PV at that age. Find payment withdrawal) anna gets each year in retirement a return identified below and sand al amount Wentified below at the end of each ye Bear's retirement income the sets her bonds with face wa 1 in stock r emeten wants the retirements t e r and inwests the prov in the stock market to as to the age specified in the datatable below that the Coupon Rate Remaining lemon handle year Anunvestment in stock fund wlation Rate for 07 Current color foron Coca-Cola Dividends Instruction Adam Question Jenna Questions 59 Type here to search A do AM D39 A J Sep 2.0TL YOUR C D 25 Question 5(12 pts: What is the value today of the Treasury Bond? T-Bond Face Value Coupon Rate Semiannual Pmt Remaining Term (years) Current YTM 35 Question 6 (12 pts: Treasury Bond PV Annual investment in stock fund Number of years Jenna saves money fyears Number of years of withdrawal years) Return on stock tund ) Step 1 PV of saving fromage 22.63 Step 2: Annual withdrawal amount berinin GO Hint: Step 1. Find how much money will Denne save by the time she retires Step 2i Once you know how much Janna saves by retirement, this will become Jenna's PV at that age Find payment withdrawal) Jenna gets each year in retirement Question 7(12 pts) Step 1: Find Cash flow for Janna from now until retirement Time Period Cash Flow Return on stock fund inflation rate Step 2: Find Fat Retirement Step 3 Jenna's first retirement withdrawal Step 4 Value of first retirment withdrawal in today's Instruction Adam - Questions 14 Jenna Questions 5.9 Type here to search Clipboard Font D39 X Step 2: Once you know how much Jenna saves by retirement, this will become Jenna's PV at that a 46 Question 7 (12 pts): Step 1: Find Cash flow for Jenna from now until retire 0 1 2 3 4 5 Return on stock fund , %6) Inflation ratele) Step 2: Find FV at Retirement Step 3: Jenna's first retirement withdrawal Step 4 Value of first retirment withdrawal in today's Should Jenna sell her Treasury bond and invest the proceeds in the stock fundr Give at least one reason for and against is plan? 57 Question 814 ptsh 64 Question 9(12 pts) Annual Dividend Dividend Growth Expected Dividend in one ye Instruction Adam - Questions 1-4 Jenna Questions 5.9 Type here to search o e w Font Alignment Clipboard P27 x ALB for A C D E F G H I J K L M Part 2 Integrative Data Case 3. This case draws on material from Chapters 3-7. Adam has just graduated, and has a good job at a decent starting salary. He hopes to purchase his first new car. The car 6 that Adam is considering costs $47,000. The dealer has given him three payment options: 1. Zero percent financing. Make a $2.800 down payment from his savings and finance the remainder with a 0% 7 APR loan for 60 months. Adam has more than enough cash for the down payment, thanks to generous graduation gifts. 8 2. Rebate with no money down. Receive a $5,000 rebate from the car dealer and finance the rest with a standard 60- 9 month loan, with an 4.95% APR. He likes this option, as he could think of many other uses for the $2,800 of his saving. 103. Pay cash. Get the $5,000 rebate and pay the rest with cash. While Adam doesn't have balance of the car cost in 11 hand, he wants to evaluate this option. His parents always paid cash when they bought a family car: Adam wonders if 12 this really was a good idea. 13 14 Adam's fellow graduate, Jenna, has been trying to decide how much of her new salary she could save for retirement. Jenna is 15 considering putting $3,000 of her annual savings in a stock fund. She just tumed 22 and has a long way to go until retirement at Instruction Adam Questions 1-4 Jenna - Questions 5-9 Type here to search Font L M No 16 age 60, and she considers this risk level reasonable. The fund she is looking at has earned an average of 6.10% over the past 15 17 years and could be expected to continue earning this amount, on average. While she has no current retirement savings, cight years 18 ago Jenna's grandparents gave her a new 30-year U.S. Treasury bond with a $20,000 face value with 4.25% semiannual coupons. Jenna wants to know her retirement income if she both (1) sells her Treasury bond at its current market value and invests the proceeds in the stock fund and (2) saves an additional $3,000 at the end of each year in the stock fund from now until she retires. Once she retires, Jenna wants those 20 savings to last until she is 94. 23 Case Questions 24 1. What are the cash flows associated with each of Adam's three car financing options? 25 2. Suppose that, similar to his parents, Adam had plenty of cash in the bank so that he could easily afford to pay cash for the car without running 26 into debt now or in the foreseeable future. If his cash carns interest at a 5.4% APR (based on monthly compounding) at the bank, what would be his 27 best purchase option for the car? 28 3. In fact, Adam doesn't have sufficient cash to cover all his debts including his substantial) student loans. The loans have a 10% APR, and any 29 money spent on the car could not be used to pay down the loans. What is the best option for Adam now? (Hint: Note that having an extra $1 today 30 saves Adam roughly $1.10 next year because he can pay down the student loans. So, 10% is Adam's time value of money in this case.) 31 4. Suppose instead Adam has a lot of credit card debt, with an 18% APR, and he doubts he will pay off this debt completely before he pays off 32 the car. What is Adam's best option now? Instruction Adam Questions 1-4 Jenna - Questions 5-9 & Type here to search 27 X NO 1 4. Suppose instead Adam has a lot of credit card debt, with an 18% APR, and he doubts he will pay off this debt completely before he pays off 32 the car. What is Adam's best option now? 33 5. Suppose Jenna's Treasury bond has a coupon interest rate of 4.25%, paid semiannually, while current Treasury bonds with the same maturity 34 date have a yield to maturity of 2.90 % (expressed as an APR with semiannual compounding). If she has just received the bond's 16th coupon, what is 35 the value of Jenna's Treasury Bond today? 36 6. Suppose Jenna sells the bond, reinvests the proceeds, and then saves as she planned. If, indeed, Jenna earns a 6.10% annual return on her 37 savings, how much could she withdraw each year in retirement? (Assume she begins withdrawing the money from the account in equal amounts at the 38 end of each yehr once her retirement begins.) 39 7. Jenna expects her salary to grow regularly. While there are no guarantees, she believes an increase of 2.50% a year is reasonable. She plans to 40 save $3,000 the first year, and then increase the amount she saves by the amount of her annual salary increase. Unfortunately, prices will also grow 41 due to inflation. Suppose Jenna assumes there will be 1.90% inflation every year. In retirement, she will need to increase her withdrawals each year to 42 keep up with inflation a. How much money will Jenna have at her retirement? b. How much can she withdraw at the end of the first year of her retirement in today's dollars? 45 Hint: Value Jenna's Retirement Fund at Retirement Age = FV of Treasury Bond + FV of Jenna's Savings 8. Should Jenna sell her Treasury bond and invest the proceeds in the stock fund? Give at least one reason for and against this plan. 9. At the last minute, Jenna considers investing in Coca-Cola stock at a price of $53.48 per share instead. The stock just paid an annual 48 dividend of $1.60 and she expects the dividend to grow at 4.00% annually. If the next dividend is due in one year, what expected return is Coca-Cola stock offering? Instruction Adam - Questions 1-4 Jenna - Questions 50 Type here to search $47.000 NOM ethnomation to solve the data case in addition to other necessary information con t ext $5,000 olemmin months Option 1 Down Payment Option 2 and 3 Rebate Option 2 loan rate Deposit Interest rates as Given in Text ( 222 Down Payment Amount Financed Interest Rate APRI Monthly Payment ROBA Instruction Adam Questions 14 Jenna - Questions - Type here to search o F36 Cash flow for Month Cash flow for Month 1 to 48 Option 1-Zero percent financing Option 2 - Rebate w/ So down Option 3 - Pay cash Down Payment APR 22 Question 2 (12 pts): of Car Financing at Deposit APR Which option should he select? Question 3 (12 pts Down Payment APR Por Car Financing at rudent Loan APR Which option should he select Option1 Option 2 Option 3 APR 3+ Question 4 (12 pts): PV of Car Financing at Credit Card APR Option 1 Instruction Adam - Questions 1-4 Jenna - Questions 5-9 Type here to search ron D X Step 2: Once you know how much Jenna saves by retirement, this will become Jenna's PV at that age. Find payment (withdrawal) Jenna gets each year in retirement. market a returnideated below a n amount identified beat the end of each you 2 retirement income i sherbet T-bonds with lace we kentified bow and let the proceeds is the $20,000 NOTE: the formation to the datacen t o other mation in the textbook A Termon Bond layar vestment in stod und Manu Return on Stock Fund Desired retirementare Planeed be expectanyen year lenna's expected annual salary increases for Inflation Rate for Current Coca Cola Mock Price for 09 Coca Cola Annual Dividend paid Coca-Cola Expected Dividend Growth Questions w e r e ond! Instruction Adam - Questions 1-4 Jenna Questions 9 Type here to search 039 - X Step 2: Once you know how much Jenna saves by Peulement, uns wil wee t 29 Questions (12 pts What is the value today of the Treasury Coupon Rate Remaining ermlyears) 36 Question (12 pts) Treasury Bond Pv Step Find how much money will Jenna save by the time she retires Step 21 Once you know how much Jenna saves by retirement, this will become enna's PV at that pe Find payment withdrawan at each yearin 46 Question 7(12 pts Step 1: Find Cash flow for denne from now until Ratunon stock fund. 91 Step 2 Se Instruction Adam - Questions 14 Jenna - Questions 5-9 Type here to search ORIN W X 46 Cuestion (12 pt: Step 1: Find Cash flow for Jenna from now until retirement Time Period Return on stock fund Inflation ratele Step 2 Find Pat Retirement Step 3 Jenna's first retirement withdrawal Step 4 Value of first retirment withdrawal in today's Should Jenna sell her Treasury bond and invest the proceeds in the stuk fund Give at least one reason for and against this plan? 57 Question 84 pts: 4 Question 9(12 pts Coca-Cola stock Dividend growth Instruction Adam Questions 1-4 Jenna - Questions 5.9 Type here to search BTW. . .A. Merge Center - $ - % 848 Conditional Format as Cell Formatting Table Styles Styles Number : Clipboard Alignment FontS A43 - X a. How much money will Jenna have at her retirement? DEFGH M Part 2 Integrative Data Case 3. This case draws on material from Chapters 3-7. Adam has just graduated, and has a good job at a decent starting salary. He hopes to purchase his first new car. The car that 6 Adam is considering costs $47,000. The dealer has given him three payment options: 1 Zero percent financing. Make a $2.800 down payment from his savings and finance the remainder with a 0% APR loan for 7 60 months. Adam has more than enough cash for the down payment, thanks to generous graduation gifts. 82 Rebate with no money down. Receive a $5,000 rebate from the car dealer and finance the rest with a standard 60 month 9 loan, with an 4 95% APR. He likes this option, as he could think of many other uses for the $2,800 of his saving 10 3. Pay cash. Get the $5,000 rebate and pay the rest with cash. While Adam doesn't have balance of the car cost in hand, he 11 wants to evaluate this option. His parents always paid cash when they bought a family car: Adam wonders if this really was a good 12 idea 14 Adam's fellow graduate, Jenna, has been trying to decide how much of her new salary she could save for retirement. Jenna is considering 15 putting $3,000 of her annual savings in a stock fund. She just turned 22 and has a long way to go until retirement at age 60, and she considers 16 this risk level reasonable. The fund she is looking at has earned an average of 6.10% over the past 15 years and could be expected to 17 continue earning this amount, on average. While she has no current retirement savings, eight years ago Jenna's grandparents gave her a new Instruction Adam - Questions 1-4 Jenna - Questions 5-9 Type here to search Formatting Font Number Argent Cells Styles X a. How much money will jenna have at her retirement? No2 16 this risk level reasonable. The fund she is looking at has earned an average of 6.10% over the past 15 years and could be expected to 17 continue earning this amount, on averape. While she has no current retirement savings, eight years ago Jenna's grandparents gave her a new 18 30-year U.S. Treasury bond with a $20,000 face value with 4.25% semiannual coupons Jenna wants to know her retirement income if she both (1) sells her Treasury bond at its current market value and invests the proceeds in the stock fund and (2) 20 saves an additional $3,000 at the end of each year in the stock fund from now until she retires. Once she retires, Jenna wants those savings to last until she is 94 23 Case Questions 24 1. What are the cash flows associated with each of Adam's three cat financing options? 25 2. Suppose that, similar to his parents, Adam had plenty of cash in the bank so that he could easily afford to pay cash for the car without running into debt 26 now or in the foreseeable future. If his cash cans interest at a 5.4% APR (based on monthly compounding) at the bank, what would be his best purchase option for 27 the cat? 20 3. In fact, Adam doesn't have sufficient cash to cover all his debts including his substantial student loans. The loans have a 10% APR, and any money spent 23 on the car could not be used to pay down the loans What is the best option for Adam now? ( Note that having an extra S1 today saves Adam roughly $1.10 30 next year because he can pay down the student loans. So, 10% is Adam's time value of money in this case) 31 4 Suppose instead Adun has a lot of credit card debt, with an 189. APR, and he doubts he will pay off this debt completely before he pays off the car. What 12 is Adam's best option ou 30 5. Suppose Jenna's Treasury bond has a coupon interest rate of 4.25%, paid semia lly, while current Treasury bonds with the same maturity date have a to mshion a nd ADR camisam ifche hatch the band's 16th on what the be of lana's Instruction Adam Questions 1-4 Jenna Cuestions 5-9 Type here to search o Mew W. General Times New Roma - 14 - AA BIU. . . A Wrap fert Merge & Center : + $ -% 988 HEMEN Conditional Format as Cell Insert Delete Form Formatting Table Styles Styles Cells Clipboard Alignment Font A43 1 X Sea. How much money will Jenna have at her retirement? N O C 314. Suppose instead Adam has a lot of credit card debt, with an 18% APR, and be doubts he will pay off this debt completely before he pays off the car. What 32 is Adam's best option now? 33 5. Suppose Jenna's Treasury bond has a coupon interest rate of 4.25%, paid semiannually, while current Treasury bonds with the same maturity date have a 34 yield to maturity of 2 90 % (expressed as an APR with semiannual compounding). If she has just received the bond's 16th coupon, what is the value of Jenna's 35 Treasury Bond today? 36 6. Suppose Jenna sells the bond, reinvests the proceeds, and then saves as she planned. If indeed, Jenna earns a 6.10% annual return on her savings, how 37 much could she withdraw each year in retirement? (Assume she begins withdrawing the money from the account in equal amounts at the end of each year once her 38 retirement begins.) 7 Jenna expects her salary to grow regularly. While there are no guarantees, she believes an increase of 2.50% a year is reasonable. She plans to save $3,000 the first year, and then increase the amount she saves by the amount of her annual salary increase. Unfortunately, prices will also grow due to inflation Suppose Jenna assumes there will be 1.90% inflation every year. In retirement, she will need to increase her withdrawals each year to keep up with inflation a How much money will Jenna have at her retirement? b. How much can she withdraw at the end of the first year of her retirement in today's dollars? Hint Value Jenna's Retirement Fund at Retirement Age = FV of Treasury Bond-FV of Jenna's Savings 8. Should Jenna sell her Treasury bond and invest the proceeds in the stock fund? Give at least one reason for and against this plan. 9. At the last minute, Jerna considers investing in Coca-Cola stock at a price of $53.48 per share instead. The stock just paid an annual dividend of 43 $1.60 and she expects the dividend to grow at 4.00% annually. If the next dividend is due in one year, what expected return is Coca-Cola stock offering? Instruction Adam questions Ben Questions Type here to search o i me W X Data Review View Help Search AutoSum - Ay Home Insert Page Layout Formulas Calibri -12 - BIU.aA E- 2. Wrap Test Merge Center - Conditional Formatas Cell Formatting Table Styles Insert Delete Format - $ . % 9 28 Sort & Filters Clear Editing Styles Cem Alignment addition to other neces a ration contained $47.000 NOTE Use this information to solve the data can Adam's car purchase Car cost Loan Termin months Option 1 Down Payment Option 2 and 3 lebate Option 2 Loan rate Deposit interest Rates as Glven in Text 222 Down Payment Instruction Adam Questions 1-4 Questions 5-9 Type here to search BIU - - A.A. Merge & Center - $ -% 688 Conditional Formatas Cell Formatting" Table Styles Styles Insert Delete For - cells Alignment Number Chipboard F36 : * fer Cash M onth tashow for Month 1 to Question 2 (12 ) PV of Car Financing at Down Payment N arinancinta Instruction Adam Questions 1.4 Jenna Questions 5.9 Type here to search Data Review View Help Search File AX Home insert Page Layout Formulas Calibri 12 AA BIU.2.0.A. Wrap Text Autosum - 47 Sort rt Delete Format To Clean Filter 0 Find & Select - 1 deas C e Merge Center - $ . % 9 .28 Condition Formatting Table Styles Editing 03 - X Step 2: Once you know how much Jenna saves by retirement, this will become Jenna's PV at that age. Find payment withdrawal) anna gets each year in retirement a return identified below and sand al amount Wentified below at the end of each ye Bear's retirement income the sets her bonds with face wa 1 in stock r emeten wants the retirements t e r and inwests the prov in the stock market to as to the age specified in the datatable below that the Coupon Rate Remaining lemon handle year Anunvestment in stock fund wlation Rate for 07 Current color foron Coca-Cola Dividends Instruction Adam Question Jenna Questions 59 Type here to search A do AM D39 A J Sep 2.0TL YOUR C D 25 Question 5(12 pts: What is the value today of the Treasury Bond? T-Bond Face Value Coupon Rate Semiannual Pmt Remaining Term (years) Current YTM 35 Question 6 (12 pts: Treasury Bond PV Annual investment in stock fund Number of years Jenna saves money fyears Number of years of withdrawal years) Return on stock tund ) Step 1 PV of saving fromage 22.63 Step 2: Annual withdrawal amount berinin GO Hint: Step 1. Find how much money will Denne save by the time she retires Step 2i Once you know how much Janna saves by retirement, this will become Jenna's PV at that age Find payment withdrawal) Jenna gets each year in retirement Question 7(12 pts) Step 1: Find Cash flow for Janna from now until retirement Time Period Cash Flow Return on stock fund inflation rate Step 2: Find Fat Retirement Step 3 Jenna's first retirement withdrawal Step 4 Value of first retirment withdrawal in today's Instruction Adam - Questions 14 Jenna Questions 5.9 Type here to search Clipboard Font D39 X Step 2: Once you know how much Jenna saves by retirement, this will become Jenna's PV at that a 46 Question 7 (12 pts): Step 1: Find Cash flow for Jenna from now until retire 0 1 2 3 4 5 Return on stock fund , %6) Inflation ratele) Step 2: Find FV at Retirement Step 3: Jenna's first retirement withdrawal Step 4 Value of first retirment withdrawal in today's Should Jenna sell her Treasury bond and invest the proceeds in the stock fundr Give at least one reason for and against is plan? 57 Question 814 ptsh 64 Question 9(12 pts) Annual Dividend Dividend Growth Expected Dividend in one ye Instruction Adam - Questions 1-4 Jenna Questions 5.9 Type here to search o e w Font Alignment Clipboard P27 x ALB for A C D E F G H I J K L M Part 2 Integrative Data Case 3. This case draws on material from Chapters 3-7. Adam has just graduated, and has a good job at a decent starting salary. He hopes to purchase his first new car. The car 6 that Adam is considering costs $47,000. The dealer has given him three payment options: 1. Zero percent financing. Make a $2.800 down payment from his savings and finance the remainder with a 0% 7 APR loan for 60 months. Adam has more than enough cash for the down payment, thanks to generous graduation gifts. 8 2. Rebate with no money down. Receive a $5,000 rebate from the car dealer and finance the rest with a standard 60- 9 month loan, with an 4.95% APR. He likes this option, as he could think of many other uses for the $2,800 of his saving. 103. Pay cash. Get the $5,000 rebate and pay the rest with cash. While Adam doesn't have balance of the car cost in 11 hand, he wants to evaluate this option. His parents always paid cash when they bought a family car: Adam wonders if 12 this really was a good idea. 13 14 Adam's fellow graduate, Jenna, has been trying to decide how much of her new salary she could save for retirement. Jenna is 15 considering putting $3,000 of her annual savings in a stock fund. She just tumed 22 and has a long way to go until retirement at Instruction Adam Questions 1-4 Jenna - Questions 5-9 Type here to search Font L M No 16 age 60, and she considers this risk level reasonable. The fund she is looking at has earned an average of 6.10% over the past 15 17 years and could be expected to continue earning this amount, on average. While she has no current retirement savings, cight years 18 ago Jenna's grandparents gave her a new 30-year U.S. Treasury bond with a $20,000 face value with 4.25% semiannual coupons. Jenna wants to know her retirement income if she both (1) sells her Treasury bond at its current market value and invests the proceeds in the stock fund and (2) saves an additional $3,000 at the end of each year in the stock fund from now until she retires. Once she retires, Jenna wants those 20 savings to last until she is 94. 23 Case Questions 24 1. What are the cash flows associated with each of Adam's three car financing options? 25 2. Suppose that, similar to his parents, Adam had plenty of cash in the bank so that he could easily afford to pay cash for the car without running 26 into debt now or in the foreseeable future. If his cash carns interest at a 5.4% APR (based on monthly compounding) at the bank, what would be his 27 best purchase option for the car? 28 3. In fact, Adam doesn't have sufficient cash to cover all his debts including his substantial) student loans. The loans have a 10% APR, and any 29 money spent on the car could not be used to pay down the loans. What is the best option for Adam now? (Hint: Note that having an extra $1 today 30 saves Adam roughly $1.10 next year because he can pay down the student loans. So, 10% is Adam's time value of money in this case.) 31 4. Suppose instead Adam has a lot of credit card debt, with an 18% APR, and he doubts he will pay off this debt completely before he pays off 32 the car. What is Adam's best option now? Instruction Adam Questions 1-4 Jenna - Questions 5-9 & Type here to search 27 X NO 1 4. Suppose instead Adam has a lot of credit card debt, with an 18% APR, and he doubts he will pay off this debt completely before he pays off 32 the car. What is Adam's best option now? 33 5. Suppose Jenna's Treasury bond has a coupon interest rate of 4.25%, paid semiannually, while current Treasury bonds with the same maturity 34 date have a yield to maturity of 2.90 % (expressed as an APR with semiannual compounding). If she has just received the bond's 16th coupon, what is 35 the value of Jenna's Treasury Bond today? 36 6. Suppose Jenna sells the bond, reinvests the proceeds, and then saves as she planned. If, indeed, Jenna earns a 6.10% annual return on her 37 savings, how much could she withdraw each year in retirement? (Assume she begins withdrawing the money from the account in equal amounts at the 38 end of each yehr once her retirement begins.) 39 7. Jenna expects her salary to grow regularly. While there are no guarantees, she believes an increase of 2.50% a year is reasonable. She plans to 40 save $3,000 the first year, and then increase the amount she saves by the amount of her annual salary increase. Unfortunately, prices will also grow 41 due to inflation. Suppose Jenna assumes there will be 1.90% inflation every year. In retirement, she will need to increase her withdrawals each year to 42 keep up with inflation a. How much money will Jenna have at her retirement? b. How much can she withdraw at the end of the first year of her retirement in today's dollars? 45 Hint: Value Jenna's Retirement Fund at Retirement Age = FV of Treasury Bond + FV of Jenna's Savings 8. Should Jenna sell her Treasury bond and invest the proceeds in the stock fund? Give at least one reason for and against this plan. 9. At the last minute, Jenna considers investing in Coca-Cola stock at a price of $53.48 per share instead. The stock just paid an annual 48 dividend of $1.60 and she expects the dividend to grow at 4.00% annually. If the next dividend is due in one year, what expected return is Coca-Cola stock offering? Instruction Adam - Questions 1-4 Jenna - Questions 50 Type here to search $47.000 NOM ethnomation to solve the data case in addition to other necessary information con t ext $5,000 olemmin months Option 1 Down Payment Option 2 and 3 Rebate Option 2 loan rate Deposit Interest rates as Given in Text ( 222 Down Payment Amount Financed Interest Rate APRI Monthly Payment ROBA Instruction Adam Questions 14 Jenna - Questions - Type here to search o F36 Cash flow for Month Cash flow for Month 1 to 48 Option 1-Zero percent financing Option 2 - Rebate w/ So down Option 3 - Pay cash Down Payment APR 22 Question 2 (12 pts): of Car Financing at Deposit APR Which option should he select? Question 3 (12 pts Down Payment APR Por Car Financing at rudent Loan APR Which option should he select Option1 Option 2 Option 3 APR 3+ Question 4 (12 pts): PV of Car Financing at Credit Card APR Option 1 Instruction Adam - Questions 1-4 Jenna - Questions 5-9 Type here to search ron D X Step 2: Once you know how much Jenna saves by retirement, this will become Jenna's PV at that age. Find payment (withdrawal) Jenna gets each year in retirement. market a returnideated below a n amount identified beat the end of each you 2 retirement income i sherbet T-bonds with lace we kentified bow and let the proceeds is the $20,000 NOTE: the formation to the datacen t o other mation in the textbook A Termon Bond layar vestment in stod und Manu Return on Stock Fund Desired retirementare Planeed be expectanyen year lenna's expected annual salary increases for Inflation Rate for Current Coca Cola Mock Price for 09 Coca Cola Annual Dividend paid Coca-Cola Expected Dividend Growth Questions w e r e ond! Instruction Adam - Questions 1-4 Jenna Questions 9 Type here to search 039 - X Step 2: Once you know how much Jenna saves by Peulement, uns wil wee t 29 Questions (12 pts What is the value today of the Treasury Coupon Rate Remaining ermlyears) 36 Question (12 pts) Treasury Bond Pv Step Find how much money will Jenna save by the time she retires Step 21 Once you know how much Jenna saves by retirement, this will become enna's PV at that pe Find payment withdrawan at each yearin 46 Question 7(12 pts Step 1: Find Cash flow for denne from now until Ratunon stock fund. 91 Step 2 Se Instruction Adam - Questions 14 Jenna - Questions 5-9 Type here to search ORIN W X 46 Cuestion (12 pt: Step 1: Find Cash flow for Jenna from now until retirement Time Period Return on stock fund Inflation ratele Step 2 Find Pat Retirement Step 3 Jenna's first retirement withdrawal Step 4 Value of first retirment withdrawal in today's Should Jenna sell her Treasury bond and invest the proceeds in the stuk fund Give at least one reason for and against this plan? 57 Question 84 pts: 4 Question 9(12 pts Coca-Cola stock Dividend growth Instruction Adam Questions 1-4 Jenna - Questions 5.9 Type here to search