Solver (Recovered)

Question:   I acquired a loan of $20,000. The repayment period is 3 years at 10% per annum.  Is there a way to work out the current repayment? I can afford to pay $1000 per month and would like to know the new repayment period too. 

Answer:  By first using the Payment function, and then using the solver option one can find a solution.

Why:  To calculate the new repayment period for a loan amount.

Applies To:  MS Excel 2003, 2007 & 2010  

1.  Using the example given above, create a worksheet as per the screen below





2.  Work out the current installment using the payment function. Select cell C7, (Monthly Payment), and type: =PMT(C6/12,C5,-C4)

3.  Calculates the payment for a loan based on constant payments and a constant interest rate

4.  PMT(rate, nper, pv, [fv], [type])

Rate Required. The interest rate for the loan

Nper Required. The total number of payments for the loan

Pv Required. The present value or the total amount that a series of future payments is worth now; also known as the principal

Fv Optional. The future value or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type Optional. The number 0 (zero) or 1 and indicates when payments are due

5.  The answer will be $645.34

6.  Now select Solver  under Data –What If Analysis- Data tools group

7.  If the solver option is not installed  select the link below for instructions on how to add the solver–in.pdf

8.  When you add the solver option select as given below


9.  Make changes as illustrated below







10.  Select the Add button and effect the changes below




11.  Select OK

12.  Select the Solve button and refer to the screen shot below





13.  Select OK


The new repayment period will be 22 months.  Thus one can easily calculate how long it will take to settle a loan amount based on new variables. The interest rate is envisaged not to exceed 15% in the stipulated period. The answer, sensitivity and limits reports have also been generated to the left of the active worksheet.