Design Layout References Mailings View Review A Sh: Tell me 11 A Aa E Styles Styles AD A Pana - I am borrowing $15,000 to buy a new equipment. I am going to make 60 end-of-month payments. The annual interest rate on the loan is 10%. The dealer will allow me to make monthly payments for Months 31 through 60 that are equal to half the monthly payments I will make for Months 1 through 30. Create an amortization table that shows the total payment, the interest and the principal for each period. You have to use Solver, and not the PMT function, to determine the monthly payments. Follow the steps below: 1. Arst list the inputs (refer to the snapshot below: Monthly rate annual rate/12, Monthly payments30 - enter a random amount here for now (Salver will work out the minimum payment later) Monthly payments60-0.5. Monthly payments30 2. Create an amortization schedule for 60 periods. Link the cells in the 'Payment column for all periods 1-30 to cell C2 (Monthly payments 30) and for all periods 31-60 to cell C/Monthly payments60). Enter formulas, as appropriate for the other columns in the table. 3. Since we do not yet know the minimum payment amount and have simply entered a random value in cell C2, the ending balance for period 60 will most likely not be zero. A snapshot of the amortization table, before I used Solver, is given below for your reference. Please note all the rows are not visible here. 1 1 Mayat . tertond what MPS + Moth tegning balance Prie The 1 Since 01. 021 310.00 6 2 0.00 500.00 12. $256 $10 7 3 SH 01231 536 $107.12 33 23 $123 300.00 SI $1255111 30 512.511 50 395 112451 33 11 2.1 $100,00 31000 $123 312300 500.00 520 SIS SUN 50.00 52013 - 5125 00 SIL S190.00 610 SILES 65 4. Now, you can use solver Target cellis C2 and you want to minimize it. Changing cell is 2 as well (yes, the target cell and the changing cell can be the samel) Constraint is cellF64 (ending balance of the 60 month). 5. Once you click on "Solve you should be able to arrive at a minimum payment of $408.09 for months 1-30 and $204 05 for months 31-60. The ending balance for the 60 month will be equal to rero ds English (United States) Focus Calibri (Bo... Aa !! ce B I Uab x, X A . Av Ev 4. Now, you can use solver Target cellis C2 and you want to minimize it. Changing cell is C2 as well (yes, the target cell and the changing cell can be the same!) Constraint is cell F64 (ending balance of the 60th month) = 0. 5. Once you click on "Solve", you should be able to arrive at a minimum payment of $408.09 for months 1-30 and $204.05 for months 31-60. The ending balance for the 60th month will be equal to zero. A B C D E F G H U 1 Monthly rate 0.008333333 =0.1/12 2 Monthly Payment(1-30) $200.00 Enter a random value here. We will use solver to find what should be the payment. 3 Monthly Payment/31-60) $ 100.00 =0.5*C2 4 Month Beginning balance Payment Interest Principal Ending balance 5 1 $15,000.00 $200.00 $125.00 $75.00 $14,925.00 6 2 $14,925.00 $200.00 $124.38 $75.63 $14,849.38 7 3 $14,849.38 $200.00 $123.74 $76.26 $14,773.12 33 29 $12,645.76 $200.00 $105.38 $94.62 $12,551.14 34 30 $12,551.14 $200.00 $104.59 $95.41 $12,455.74 35 31 $12,455.74 $100.00 $103.80 -$3.80 $12,459.53 62 58 $12,570.20 $100.00 $104.75 $4.75 $12,574,95 63 59 $12,574.95 $100.00 $104.79 -$4.79 $12,579.74 64 60 $12,579.74 $100.00 $104.83 -$4.83 $12,584.57 + 65 age 2 of 2 324 words DE English (United States) B Focus RABO MacBool co 000 Design Layout References Mailings View Review A Sh: Tell me 11 A Aa E Styles Styles AD A Pana - I am borrowing $15,000 to buy a new equipment. I am going to make 60 end-of-month payments. The annual interest rate on the loan is 10%. The dealer will allow me to make monthly payments for Months 31 through 60 that are equal to half the monthly payments I will make for Months 1 through 30. Create an amortization table that shows the total payment, the interest and the principal for each period. You have to use Solver, and not the PMT function, to determine the monthly payments. Follow the steps below: 1. Arst list the inputs (refer to the snapshot below: Monthly rate annual rate/12, Monthly payments30 - enter a random amount here for now (Salver will work out the minimum payment later) Monthly payments60-0.5. Monthly payments30 2. Create an amortization schedule for 60 periods. Link the cells in the 'Payment column for all periods 1-30 to cell C2 (Monthly payments 30) and for all periods 31-60 to cell C/Monthly payments60). Enter formulas, as appropriate for the other columns in the table. 3. Since we do not yet know the minimum payment amount and have simply entered a random value in cell C2, the ending balance for period 60 will most likely not be zero. A snapshot of the amortization table, before I used Solver, is given below for your reference. Please note all the rows are not visible here. 1 1 Mayat . tertond what MPS + Moth tegning balance Prie The 1 Since 01. 021 310.00 6 2 0.00 500.00 12. $256 $10 7 3 SH 01231 536 $107.12 33 23 $123 300.00 SI $1255111 30 512.511 50 395 112451 33 11 2.1 $100,00 31000 $123 312300 500.00 520 SIS SUN 50.00 52013 - 5125 00 SIL S190.00 610 SILES 65 4. Now, you can use solver Target cellis C2 and you want to minimize it. Changing cell is 2 as well (yes, the target cell and the changing cell can be the samel) Constraint is cellF64 (ending balance of the 60 month). 5. Once you click on "Solve you should be able to arrive at a minimum payment of $408.09 for months 1-30 and $204 05 for months 31-60. The ending balance for the 60 month will be equal to rero ds English (United States) Focus Calibri (Bo... Aa !! ce B I Uab x, X A . Av Ev 4. Now, you can use solver Target cellis C2 and you want to minimize it. Changing cell is C2 as well (yes, the target cell and the changing cell can be the same!) Constraint is cell F64 (ending balance of the 60th month) = 0. 5. Once you click on "Solve", you should be able to arrive at a minimum payment of $408.09 for months 1-30 and $204.05 for months 31-60. The ending balance for the 60th month will be equal to zero. A B C D E F G H U 1 Monthly rate 0.008333333 =0.1/12 2 Monthly Payment(1-30) $200.00 Enter a random value here. We will use solver to find what should be the payment. 3 Monthly Payment/31-60) $ 100.00 =0.5*C2 4 Month Beginning balance Payment Interest Principal Ending balance 5 1 $15,000.00 $200.00 $125.00 $75.00 $14,925.00 6 2 $14,925.00 $200.00 $124.38 $75.63 $14,849.38 7 3 $14,849.38 $200.00 $123.74 $76.26 $14,773.12 33 29 $12,645.76 $200.00 $105.38 $94.62 $12,551.14 34 30 $12,551.14 $200.00 $104.59 $95.41 $12,455.74 35 31 $12,455.74 $100.00 $103.80 -$3.80 $12,459.53 62 58 $12,570.20 $100.00 $104.75 $4.75 $12,574,95 63 59 $12,574.95 $100.00 $104.79 -$4.79 $12,579.74 64 60 $12,579.74 $100.00 $104.83 -$4.83 $12,584.57 + 65 age 2 of 2 324 words DE English (United States) B Focus RABO MacBool co 000