[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Subtract from total amount each month


My question is linked to this one: Excel Question

I would like to subtract a fixed amount to a total on the 1st of each months.

See an example of what I would like please (no interests calculation needed):
Let's say the first cell is A1 then A2, A3, A4.

The first 2 cells should have no formula whatsoever, these are fixed amounts.

total amount left to repay  | Amount paid every month | balance (A1-A2 calculated automatically every 1st of each month) | Number of months left until balance (A3) is 0

Is this possible please?
thanks so much
1 Solution
You need one more cell for the date of the first payment. Let's put that in A3
A1: total
A2: payment
A3: first payment date
A4: balance =A1-A2*(12*(YEAR(TODAY())-YEAR(A3)) + MONTH(TODAY())-MONTH(A3)-(DAY(A3)>1)+(DAY(TODAY())>1))
A5: remaining months =CEILING(A4/A2,1)
TierixAuthor Commented:
THank you so much again for your help! Works like a charm

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now