Subtract from total amount each month

Posted on 2012-08-16
Last Modified: 2012-08-16

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
Question by:Tierix
    LVL 37

    Accepted 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)

    Author Comment

    THank you so much again for your help! Works like a charm

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now