Formula to Calculate amount due

I am trying to calculate the total amount left to pay for the year on an account.My Total amount for the year is $1018.20 with the payment of $84.85 per month. I have gotten that part done, however I try to pay ahead to prevent disconnection of services. So instead of paying $84.85 per month I may send in $100.00 which will build up credit. How can I can subtract the credit from the total paid. My question is how can I account for the total owed for the year in a formula?

$1018.24 Per Year
$84.85 Per Month
2 payments of $84.85 = $ 169.70
2 payments of $100.00 = $200.00 which produces credit of $ 30.30. How can I include this credit into my yearly total and come up with the amount I owe for the year. Any help is appreciated.


Thank You

John

Pauly

Thank you for the prompt reply. I agree with your example! I pay in advance of the billing cycle thus my actual payment is more than $84.85 and this creats credit. How can I express this in a formula?

Thank You

John

I am not sure that I understand the question but seems like you are asking about calculating how much you need to pay per month, given that you have overpaid by $30.30 to this point. I believe that this would fall into the category of needing to use the 'Solver' feature. I have made a small table that shows the monthly fee, the min. payment amount, Amt. paid, deficit/(credit), and Amt. owed for year. The formulas used were

• 

  Please Login or Register  to view this content.

  for min. payment (the ifferror part is for the first month),

• 

  Please Login or Register  to view this content.

  for debit/(credit) which will be blank if there is nothing in the amt. paid column,

• 

  Please Login or Register  to view this content.

  a running total for amt. owed for year.

I filled in the first two months payments as being $100 and $84.85 for the rest, through July. To find out how much you should pay per month thereafter use the solver

• set the objective to F13,

• To: Value of: 0,

• By Changing the Variable Cells: D9:D13.

Payment Solver.xlsx

Hope this helps

You're welcome and thank you for the feedback, glad that the information is helpful. Please use the thread tools to mark the thread as 'Solved' and if anyone contributed to helping with your problem please consider adding to their reputation.