Answer in excel, please.

Problem Set #2 Fixed Payment Loan Calculation You are looking to buy a new Ford F150. The "all in out the door" price comes to $56,000. The dealer gives you two options for getting the car: (a) The dealer will give you financing at 4.9% for 60 months if you put $5,000 down. (b) If you want a lower payment with no money down, you can sign a 36 month lease for $625 per month. If you expense the lease payments to your business, you will deduct the lease payments from your tax liability each year. You estimate your effective tax rate during the 3 years will be 25%. Your task is to create a spreadsheet to model the two options from the dealer. Then create a results table showing the values needed to answer questions 3 through 8. The spreadsheet model should have the following: 1. An assumption box where you can enter the price of the car, down payment, loan amount, monthly payment, annual interest rate, monthly interest rate, lease payment and tax rate. You may add other calculated items as you see fit (i.e., annuity factor, discount factor). 2. Your loan table should have the following column headings: period, date, BOY loan bal- ance, total payment, principal payment, interest payment, EOY loan balance, cumulative interest paid, cumulative principal paid, cumulative loan payments, cumulative lease payments, and cumulative tax-adjusted lease payments. Your loan table should be all formulas with no hard numbers, such that you can change the loan amount, interest rate, lease payment, and tax rate for additional analysis. 3. Based on the dealer loan offer, what is your monthly car payment? 4. What is total interest paid out over the life of the $56,000 auto loan? 5. After 36 months, what are your total loan payments, and what is your outstanding loan balance on the car? 6. If you decide to lease, what will your total payments (no tax deduction) be after 36 months when you turn in the car? 7. If you are able to expense the lease payments to a business, what is your effective or tax-adjusted total lease payments after 36 months? 8. If you buy the car, what price would you need to sell the car after 36 months, to make your total cost equivalent to the total lease payments (no tax deduction)? Total Down Loan Ann eff Rate Monthly Rate Years 56000 Annuity 5000 Payment 51000 Lease 4.900% Tax 0.408% 5 60 53.11957018 $960 5625 256 n Sale Price 20000 0000 n 36208 LOAN BOY Payment int 1 51000 960 2 50248 960 3 49493 960 4 48735 960 2-24 5 47974 960 2012 6 47210 960 10 7 46443 960 Te 8 45672 960 . 10 9 44899 960 24403 10 44122 960 11 43342 960 12 42550 960 17:00 13 41772 960 - 14 40983 960 104 15 40190 960 16 39394 960 rer 17 38595 960 --- 18 37792 960 19 36987 960 20 960 800 21 -- 35365 960 04 22 34549 960 304 23 33730 960 04 24 32908 960 S2000 04 25 32082 960 SC00 04 26 31253 960 304 27 30421 960 04 28 29585 960 304 29 28746 960 304 30 27903 960 04 31 27057 960 32 26207 960 304 33 25354 960 04 24 24407 960 2740 304 25 23637 960 -303 04 36 22774 960 27 non 37 21002 21907 960 800 20 21036 21028 38 960 800 39 20 20162 2016 960 800 40 19284 960 804 41 41 1942 18403 960 804 12 42 17510 17518 960 804 12 43 1680 16629 960 44 15737 960 45 14841 960 46 13942 960 47 13039 960 1 48 12132 960 49 11221 960 - 50 10307 960 51 9389 960 52 8467 960 53 7542 960 54 6612 960 55 5679 960 56 4742 960 57 3802 960 58 2857 960 59 1908 960 56 non 208 205 202 199 196 193 190 186 183 180 177 174 171 167 164 161 158 154 151 148 144 174 17 141 100 138 134 134 131 128 124 121 117 117 114 110 107 101 104 104 100 100 97 93 89 88 86 82 84 79 18 75 72 68 06 64 04 61 36178 Principle EOY Loan cint c Principle c Total 752 50248 208 752 960 755 49493 413 1507 1920 758 48735 616 2265 2880 761 47974 815 3026 When 3840 764 47210 1010 3700 4800 767 46443 1203 4557 5761 770 45672 1393 5328 6721 774 44999 1579 6101 7681 777 44122 1763 6878 8641 -- 780 43342 1943 7658 9601 783 42559 2120 8441 10561 786 41772 2294 9228 11521 -- --- 790 40983 2464 10017 12481 793 40190 2632 10810 13441 796 39394 2796 11606 14401 - . 1941 - 799 38595 2956 12405 15362 --- 803 37792 3114 13208 16322 000 44000 806 36987 3268 14013 17282 1400 1024 800 36178 3419 14822 18242 812 3567 35365 014 3567 15635 19202 816 34540 019 34548 16451 3712 3114 16451 20162 819 33730 3839 3853 17270 21122 24 822 32908 3880 3990 18092 22082 826 32082 4125 vo 23042 18918 120 23044 829 31253 4256 19747 200 18747 24002 832 034 30421 4383 720- 20579 20579 24963 836 29585 28585 tuwo 4508 21415 25923 839 28746 28740 4628 7020 22254 26883 843 27903 4746 23007 1140 SUB 4107 27843 846 27057 4860 1000 43878 23943 2009 28803 050 26207 2020 4970 14 24793 24793 109 29763 853 09 25354 25646 5077 43834 30723 057 24497 5191 *** 10 COSUS 26503 31000 31683 860 we 23637 5281 140 27363 32643 864 22774 5377 33603 28226 ---- ss009 867 -10 21907 5470 w 34564 29093 48000 871 871 21036 5560 29964 35524 874 20160 87+ 28404 5646 20162 30838 36484 070 5720 19284 810 5728 31716 37444 001 19403 5007 881 20404 5807 18403 32597 38404 885 17518 500 22409 5882 33482 39364 oon 18620 5052 5953 16629 24271 1994 40324 34371 892 1572 884 11004 6021 15737 35263 41284 896 14841 6086 36159 49944 42244 899 13942 6146 37058 43204 1 - 903 13039 6203 37961 44165 . 907 12132 6256 38868 45125 911 11221 6306 ** 39779 46085 914 10307 6352 40693 47045 918 9389 6394 41611 48005 922 8467 6432 42533 48965 926 7542 6467 43458 49925 929 6612 6497 44388 50885 933 5679 6524 45321 51845 22 937 4742 6548 46258 52805 Two 941 3802 6567 47198 53765 945 2857 6583 49143 54726 948 1908 6594 49092 55686 952 956 6602 50044 56646 056 6606 510 57606 c Lease 625 1250 1875 2500 3125 - 3750 4375 5000 5625 6250 6875 7500 8125 - 8750 9375 10000 10 10625 11250 11875 12500 13125 1312 13750 14375 15000 15625 16250 16875 17500 18125 2012 18750 19375 1887 20000 wy 20625 21250 21875 22500 Total CE 5960 6920 7880 8840 9800 10761 11721 12681 13641 14601 -- 15561 16521 12 17481 18441 19401 20362 21322 22282 --- 23242 24202 24202 25162 25162 26122 27082 28042 29002 29963 30923 31883 32843 32843 33803 34763 35723 36683 37643 37048 38603 38504 39564 40524 41404 41484 42444 43404 44284 44364 45324 46284 47244 49204 49165 50125 51085 52045 53005 53965 54925 -- 55885 56845 57805 58765 59726 60686 61646 tax Adi c Lease 469 938 14061 1875 2012 2344 2813 3281 3750 4219 4688 5156 5625 6094 6563 7031 - 7500 7969 8438 8906 93751 9375 9944 987 10313 10781 10781 1128 11250 1111 11719 14100 12188 12656 13125 1 135941 1326 1700 14063 14531 15000 15469 15938 16406 16875 57 53 50 46 42 38 35 31 27 23 19 16 12 8 4 2006 Problem Set #2 Fixed Payment Loan Calculation You are looking to buy a new Ford F150. The "all in out the door" price comes to $56,000. The dealer gives you two options for getting the car: (a) The dealer will give you financing at 4.9% for 60 months if you put $5,000 down. (b) If you want a lower payment with no money down, you can sign a 36 month lease for $625 per month. If you expense the lease payments to your business, you will deduct the lease payments from your tax liability each year. You estimate your effective tax rate during the 3 years will be 25%. Your task is to create a spreadsheet to model the two options from the dealer. Then create a results table showing the values needed to answer questions 3 through 8. The spreadsheet model should have the following: 1. An assumption box where you can enter the price of the car, down payment, loan amount, monthly payment, annual interest rate, monthly interest rate, lease payment and tax rate. You may add other calculated items as you see fit (i.e., annuity factor, discount factor). 2. Your loan table should have the following column headings: period, date, BOY loan bal- ance, total payment, principal payment, interest payment, EOY loan balance, cumulative interest paid, cumulative principal paid, cumulative loan payments, cumulative lease payments, and cumulative tax-adjusted lease payments. Your loan table should be all formulas with no hard numbers, such that you can change the loan amount, interest rate, lease payment, and tax rate for additional analysis. 3. Based on the dealer loan offer, what is your monthly car payment? 4. What is total interest paid out over the life of the $56,000 auto loan? 5. After 36 months, what are your total loan payments, and what is your outstanding loan balance on the car? 6. If you decide to lease, what will your total payments (no tax deduction) be after 36 months when you turn in the car? 7. If you are able to expense the lease payments to a business, what is your effective or tax-adjusted total lease payments after 36 months? 8. If you buy the car, what price would you need to sell the car after 36 months, to make your total cost equivalent to the total lease payments (no tax deduction)? Total Down Loan Ann eff Rate Monthly Rate Years 56000 Annuity 5000 Payment 51000 Lease 4.900% Tax 0.408% 5 60 53.11957018 $960 5625 256 n Sale Price 20000 0000 n 36208 LOAN BOY Payment int 1 51000 960 2 50248 960 3 49493 960 4 48735 960 2-24 5 47974 960 2012 6 47210 960 10 7 46443 960 Te 8 45672 960 . 10 9 44899 960 24403 10 44122 960 11 43342 960 12 42550 960 17:00 13 41772 960 - 14 40983 960 104 15 40190 960 16 39394 960 rer 17 38595 960 --- 18 37792 960 19 36987 960 20 960 800 21 -- 35365 960 04 22 34549 960 304 23 33730 960 04 24 32908 960 S2000 04 25 32082 960 SC00 04 26 31253 960 304 27 30421 960 04 28 29585 960 304 29 28746 960 304 30 27903 960 04 31 27057 960 32 26207 960 304 33 25354 960 04 24 24407 960 2740 304 25 23637 960 -303 04 36 22774 960 27 non 37 21002 21907 960 800 20 21036 21028 38 960 800 39 20 20162 2016 960 800 40 19284 960 804 41 41 1942 18403 960 804 12 42 17510 17518 960 804 12 43 1680 16629 960 44 15737 960 45 14841 960 46 13942 960 47 13039 960 1 48 12132 960 49 11221 960 - 50 10307 960 51 9389 960 52 8467 960 53 7542 960 54 6612 960 55 5679 960 56 4742 960 57 3802 960 58 2857 960 59 1908 960 56 non 208 205 202 199 196 193 190 186 183 180 177 174 171 167 164 161 158 154 151 148 144 174 17 141 100 138 134 134 131 128 124 121 117 117 114 110 107 101 104 104 100 100 97 93 89 88 86 82 84 79 18 75 72 68 06 64 04 61 36178 Principle EOY Loan cint c Principle c Total 752 50248 208 752 960 755 49493 413 1507 1920 758 48735 616 2265 2880 761 47974 815 3026 When 3840 764 47210 1010 3700 4800 767 46443 1203 4557 5761 770 45672 1393 5328 6721 774 44999 1579 6101 7681 777 44122 1763 6878 8641 -- 780 43342 1943 7658 9601 783 42559 2120 8441 10561 786 41772 2294 9228 11521 -- --- 790 40983 2464 10017 12481 793 40190 2632 10810 13441 796 39394 2796 11606 14401 - . 1941 - 799 38595 2956 12405 15362 --- 803 37792 3114 13208 16322 000 44000 806 36987 3268 14013 17282 1400 1024 800 36178 3419 14822 18242 812 3567 35365 014 3567 15635 19202 816 34540 019 34548 16451 3712 3114 16451 20162 819 33730 3839 3853 17270 21122 24 822 32908 3880 3990 18092 22082 826 32082 4125 vo 23042 18918 120 23044 829 31253 4256 19747 200 18747 24002 832 034 30421 4383 720- 20579 20579 24963 836 29585 28585 tuwo 4508 21415 25923 839 28746 28740 4628 7020 22254 26883 843 27903 4746 23007 1140 SUB 4107 27843 846 27057 4860 1000 43878 23943 2009 28803 050 26207 2020 4970 14 24793 24793 109 29763 853 09 25354 25646 5077 43834 30723 057 24497 5191 *** 10 COSUS 26503 31000 31683 860 we 23637 5281 140 27363 32643 864 22774 5377 33603 28226 ---- ss009 867 -10 21907 5470 w 34564 29093 48000 871 871 21036 5560 29964 35524 874 20160 87+ 28404 5646 20162 30838 36484 070 5720 19284 810 5728 31716 37444 001 19403 5007 881 20404 5807 18403 32597 38404 885 17518 500 22409 5882 33482 39364 oon 18620 5052 5953 16629 24271 1994 40324 34371 892 1572 884 11004 6021 15737 35263 41284 896 14841 6086 36159 49944 42244 899 13942 6146 37058 43204 1 - 903 13039 6203 37961 44165 . 907 12132 6256 38868 45125 911 11221 6306 ** 39779 46085 914 10307 6352 40693 47045 918 9389 6394 41611 48005 922 8467 6432 42533 48965 926 7542 6467 43458 49925 929 6612 6497 44388 50885 933 5679 6524 45321 51845 22 937 4742 6548 46258 52805 Two 941 3802 6567 47198 53765 945 2857 6583 49143 54726 948 1908 6594 49092 55686 952 956 6602 50044 56646 056 6606 510 57606 c Lease 625 1250 1875 2500 3125 - 3750 4375 5000 5625 6250 6875 7500 8125 - 8750 9375 10000 10 10625 11250 11875 12500 13125 1312 13750 14375 15000 15625 16250 16875 17500 18125 2012 18750 19375 1887 20000 wy 20625 21250 21875 22500 Total CE 5960 6920 7880 8840 9800 10761 11721 12681 13641 14601 -- 15561 16521 12 17481 18441 19401 20362 21322 22282 --- 23242 24202 24202 25162 25162 26122 27082 28042 29002 29963 30923 31883 32843 32843 33803 34763 35723 36683 37643 37048 38603 38504 39564 40524 41404 41484 42444 43404 44284 44364 45324 46284 47244 49204 49165 50125 51085 52045 53005 53965 54925 -- 55885 56845 57805 58765 59726 60686 61646 tax Adi c Lease 469 938 14061 1875 2012 2344 2813 3281 3750 4219 4688 5156 5625 6094 6563 7031 - 7500 7969 8438 8906 93751 9375 9944 987 10313 10781 10781 1128 11250 1111 11719 14100 12188 12656 13125 1 135941 1326 1700 14063 14531 15000 15469 15938 16406 16875 57 53 50 46 42 38 35 31 27 23 19 16 12 8 4 2006