Solver Question!

[Guest]

Hi! I have a question about solver. I will try to make this as clear as possible.
I am trying to use the solver in Excel to find the smallest amount of interest accrued over time. Let me explain.
I have 2 accounts. Let’s say one has a balance of $5000, and the other a balance of $10000. I have annual interest rates for both of them (5% and 2% respectively). I have to make a minimum monthly $100 payment on account 1 and $150 on account 2. I have a total of $400 per month to spend on payments. I have set up a way using CUMIPMT to calculate the total interest paid on each account for the duration of the loan payoff. I want to have solver adjust my payments to give me the lowest total cumulative interest paid.
Here is what I did: I set the summation of CUMIPMT to my target cell in solver and checked minimum. I told it to change the cells where them monthly payments were. I used two constraints: 1st one- the acct 1 and acct 2 payments cannot exceed $400, and the payment values must meet or exceed the minimum monthly payment amounts designated. When I have solver try to calculate this it comes back but it is not optimal. Meaning I can eyeball it and make changes to the monthly payments to get the sum of CUMIPMT even lower than solver. What am I doing wrong?

Thanks in advance!

BTW here is a copy of this problem:

Account 1 Account 2
Present Value of Account $2,000.00 $10,000.00
Minimum Payment Amount (constraints) $ 100.00 $ 150.00
Payment Amount (want solver to change these) $ 100.00 $ 126.67
Interest Rate 0.0041667 0.00166667
Number of Payments To Completion (NPER) 19 74
Number of Days 570 2220
Start Date 8/2/2004 8/2/2004
End Date 2/23/2006 8/31/2010
Cumulative Interest Paid (CUMIPMT) $ 84.37 $ 637.66
Sum of Cum Interest paid (target cell-MINIMIZE!) $ 722.72
Total amount Available to pay (constraint) $ 400.00
Sum of payments $ 226.67

 

[Dana DeLouis]

Just a question. If making very large payments, does that affect the
"Number of payments to Completion?" Do you have to pay off Account 1 in 570
Days? To me, the larger the payment, the sooner the loan would be paid off.
Solver should be able to do this, but I don't understand the problem setup
very well.
Dana

Hi! I have a question about solver. I will try to make this as clear as possible.
I am trying to use the solver in Excel to find the smallest amount of

interest accrued over time. Let me explain.

I have 2 accounts. Let's say one has a balance of $5000, and the other a

balance of $10000. I have annual interest rates for both of them (5% and 2%
respectively). I have to make a minimum monthly $100 payment on account 1
and $150 on account 2. I have a total of $400 per month to spend on
payments. I have set up a way using CUMIPMT to calculate the total interest
paid on each account for the duration of the loan payoff. I want to have
solver adjust my payments to give me the lowest total cumulative interest
paid.

Here is what I did: I set the summation of CUMIPMT to my target cell in

solver and checked minimum. I told it to change the cells where them
monthly payments were. I used two constraints: 1st one- the acct 1 and acct
2 payments cannot exceed $400, and the payment values must meet or exceed
the minimum monthly payment amounts designated. When I have solver try to
calculate this it comes back but it is not optimal. Meaning I can eyeball it
and make changes to the monthly payments to get the sum of CUMIPMT even
lower than solver. What am I doing wrong?

 

[Dana DeLouis]

Hi Jenn. Here's an idea that worked ok for me. You are trying to change the
amount to spend on each account each month. As a result, the number of
periods to pay off that loan results in a fractional number of months.
Which is ok I suppose. How about this idea?
Make the variable the number of integer months you want to pay off the loan.
The next cell calculates the payment based on the integer number of months.
(using PMT() ).
The next cell calculates the total interest for the loan using CUMIPMT, with
the period from 1 to the number of months to pay off the loan.
Have a cell that Sums the total monthly payments. (which must be less than
$400), and another cell that sums the total interest (target cell that you
are trying to minimize.)

Minimize total interest ,
subject to
number of months are "Integer"
Total payments <= $400
Each payment >= Minimum monthly payment. **
** The Key here is to do each payment >= Minimum individually. Solver won't
work well with this type of problem if you do them all on 1-line. Do each
individually.

** Another Key to make this work is to select Solver's "Options", and select
"Central Derivative" instead of the default "Forward Derivative."

The solution I got was to pay off account 1 in 20 months paying $104.43, and
account #2 in 35 months paying $294.37.
Total monthly payments were $398.80
Minimum interest of: $391.48

Please let me know if you find a better solution.
HTH. :>)
Dana DeLouis

You don't have to payoff the accounts at any set time but the limiting

resource is the money you have available to pay accounts. For my example I
have 2 accounts but in the real problem I have 6.