Excel Wizards

[Keod2]

I have a random question for any MS excel wizards out there, does anybody know what formula to use in order to track my current loan amount in excel?

So if I give an example if I had a loan of £4000 and was repaying £100 a month I want excel to know which month it is currently now and minus £100 off of the total loan amount without input from me. For example if the loan was taken out in July in August I want excel to show the loan as £3900 automatically as it now detects its august and has taken this amount away from it.

I want to do this so I can keep track of my families loans as logging into the many various accounts is quite tedious. I always pay my monthly direct debits so the excel document will stay true to life.

Thankyou in advance!

 

[Greboth]

The TODAY() function is what you'll need for this as it'll use your system date to know when now is so can automatically update as a date gets further in to the past.

Do you want it to take in to account the day of the month? So if you took the loan out today, 25 July, the £100 for August wouldn't get deducted until 25 August? Or just that anytime you open the document in August it's deducted the £100?

If you want to include the day. In the below, I'm using "A1" as the cell containing the date the loan started
=DATEDIF(A1,TODAY(),"m")

Or, alternatively you can hardcode the date with
=DATEDIF("25/07/2024",TODAY(),"m")

This formula simply returns the number of months between 2 dates but it does take in to account the day. So 25/01/2024 to today would return 6 months but 26/01/2024 to today would only return 5 months as the day anniversary hasn't passed yet.


If you don't care about the day and just want to it deduct the £100 as soon as it's August then the below should work. Again, assuming "A1" being the cell containing the date the loan started
=MONTH(TODAY())-MONTH(A1)+12*(YEAR(TODAY())-YEAR(A1)). Again, like above, if you want to hardcode the date you can again replace A1 with the relevant date surrounded by " ".

This formula is basically the long form version of the above using the MONTH function to pull a number (1-12) for the relevant month in the date. The problem is this totally ignores the year aspect of the date so 25/07/2024 and 25/07/2023 both simply return 7 for July, so the subtraction would equal 0 rather than 12. Hence the requirement to add the 12 x the difference in year values. Unlike the above, this formula completely ignores the day part of the date so any date in January 2024 to any date in July 2024 will always return 6 months.

 

[Keod2]

Hi thankyou for your reply, I dont mind about it being the specific date just the overall month ie July, August etc, with your formula where am I putting the -100 and where do I tell it which cell the overall amount is, ie the 4000? My knowledge of excel isnt fantastic so you will have to spoon feed me somewhat lol

 

[Greboth]

Ah my bad, I thought it was just the month part you were struggling with.

In that case, I'm working on
A1 is the date of loan / date of loan payment
A2 is the initial loan amount
A3 is the monthly repayment amount

Then
=A2-(DATEDIF(A1,TODAY(),"m")*A3)

When I just tried it, Excel tried to be helpful and automatically changed it to a date format when I input the above formula. But it's easy enough to right click on the cell, format and change it to currency with whatever options you want for decimal places / symbol.

 

[Keod2]

Thankyou for further explaining, so I used the formula and it is coming back as 4000, is that because it sees this as the first month and from august it will deduct the correct amount?
https://i.imgur.com/1zAmKR9.png

 

[Greboth]

Thankyou for further explaining, so I used the formula and it is coming back as 4000, is that because it sees this as the first month and from august it will deduct the correct amount?
https://i.imgur.com/1zAmKR9.png

Correct

If you want to test it is working, you can change the date in A1 to 25/06/2024 and it should deduct 1 month, 25/05 2 months, 25/04 3 months etc.

Though re-reading post#3 I think I've given you the wrong formula as I misinterpreted your I don't mind about the date as you don't care either way but re-reading it, it's I don't care I don't want it to use the day. So the above does use the day so, as per your screenshot, it won't deduct the first £100 until 25/08/2024 as that is 1 month later (and the 25 of every month after that)

If you want it to deduct the first month as soon as we're in to the next month (so on 1 August 2024 and then on the 1st of every month after) you need to change to the other formula in post 2. So it would be

=A2-(MONTH(TODAY())-MONTH(A1)+12*(YEAR(TODAY())-YEAR(A1))*A3)

 

[Keod2]

Thankyou very much I have implemented this on my spreadsheet now and I can confirm that it is working! You are indeed a wizard