J
Jenn
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
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