Forum Discussion
How to assign a function to calculate a decreasing balance.
I have a balance on a child support debt. I want to devise an Excel formula to subtract each payment from the previous balance to create a new balance. I want show this for each month for several years, by month. What is the form
2 Replies
- in simple terms
=[old balance]-[new payment]
that is the best I can do because I have no idea how your sheet is set up. Is each month a different column, different row, different sheet, different file?
You might be better using another formula like:
=[$Initial Balance] - SUM( [$First Payment]:[This Payment] )
In this case I added those $ to 'lock' those cells so the only cell reference changing is [This Payment] so each new copy of it will calculate the new balance. In some cases this may be easier than trying to refer to the previous Balance.
If you share a sample sheet I'm sure we could help much more.Yup mtarler, is correct in his logic and explanation. I've added a simple visual for you to use for a simple running sum across rows and columns. See example below where $55,000 would be the total you're subtracting from:
Formula for running sum in column: =SUM($P$7:$R7)-55000
Formula for running sum across the row: =SUM($P$7:P9)-55000
