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. First list the inputs (refer to the snapshot below):

• Monthly rate = annual rate/12,
• Monthly payments30 enter a random amount here for now (Solver 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 payments30) and for all periods 31-60 to cell C3(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.

4. Now, you can use solver

• Target cell is 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 60^th month) = 0.

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^th month will be equal to zero.

Show how to use solver on excel to get answer

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