0

I am creating a weekly payment schedule to pay off a loan, and I am using Solver to determine the weekly payment I should make to pay off the loan by a certain date where the payment amount remains the same (e.g. the cell below is equal to the cell above). The balance adjusts for the interest and time. Here is the format of the spreadsheet:

Date Payment Balance
3/1/2023 $100 x
3/8/2023 $100 y
... ... ...
3/1/2026 $100 $0.00

Simple enough right? I am using Solver to set the last cell under the balance column to equal $0 by changing the first cell which is currently labeled at $100. However, I am getting an error that says "Constraints Which Make the Problem Infeasible", but I am not using any constraints at all. What am I doing wrong?

I tried using Solver after originally trying to solve this using Goal Seek. The value at the end of the balance is fluctuating between $0.01 and -$0.01 before goal seek tells me that they "may not have found a solution". Throughout this process however, I see what my payment should be through the iterations, so technically my prompt has been answered.

I guess here are my main questions are regarding the functionality of Goal Seek and Solver:

  1. Why is Solver giving me the infeasible solution prompt?
  2. Why doesn't Goal Seek find the exact value for 0 which I know exists?

Thanks in advance. Although my prompts have been answered, I guess I want to have a better understanding why these functions aren't working properly for future references.

Edit: In the spreadsheet I am using, Goal Seek does sometimes compute an exact value to 12 decimal places. Is the issue that some scenarios require more than 12 decimal places to find an exact answer?

Toto
  • 17,001
  • 56
  • 30
  • 41
Phil
  • 1
  • 1
  • Not an answer to your question, but goal seek/solver seems like overkill as you would simply take the total amount owed divided by the number of periods you are paying (+/- rounding which I think is an issue you are running up against with goal seek). Of course adding interest complicates things, but that is where the `PMT` formula should work. The final caveat is if you are modeling unequal payments which would be better as a amortization table like you have. – gns100 Feb 28 '23 at 18:17
  • Thanks @gns100 for your suggestion. PMT was a completely oversight on my part. The way I set up my spreadsheet made it seem like Goal Seek / Solver was the optimal solution. I guess it leaves me to wonder why I wasn't able to solve it using these functionalities if someone could attest to that. – Phil Feb 28 '23 at 18:51

0 Answers0