Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculate payments left.

0

is there a way to calculate payments left or remaining balance.

with this lease there are no benefits to paying it early as the total ammount stays the same. ie you don't save on interest.

I have it so it automatically highlights all payments made. so I am wondering if I can get excel to calulate how many payments have been made and the balnce.

I have attached file

excelfinance
MS Office Version: 2019
posted by: wildecoyote1966 (rep: 30) on Thu Dec 16, 2021 at 8:13 pm
Answer
Discuss

Answers

0
Selected Answer

Albert

Like your highlighting (conditional formatting), I've used today's date in $C$2 to work out how many payments to count. The "payments" made formula in D17 of the revised workbook attached is:

=COUNTIF(B24:B205,"<"&$C$2)
I've then used that number to get the current balance in D18 using OFFSET to get the value (that many rows down and 4 rows to the right of cell A23):
=OFFSET(A23,D17,4)
Hope this helps.

.

.

posted by: John_Ru (rep: 6142) on Fri Dec 17, 2021 at 3:48 am
Discuss

Discussion

Thanks again John.

I used to think I was reasonably good with excel but then I didn't use it for a few years and then came on here and well I'm not lol.

and I never even heard of offset
wildecoyote1966 (rep: 30) Dec 17, '21 at 5:46 pm
Glad it worked, Albert.

Offset is very handy in VBA I find but not well known as a worksheet function (of which there are loads of useful ones thankfully!)
John_Ru (rep: 6142) Dec 17, '21 at 6:36 pm
Hi John

sometimes I wish I could send you a file I've been working on for years (a budget thing) but I keep changing it and it never seems to get finished lol.

thanks for all your help
Albert
wildecoyote1966 (rep: 30) Dec 17, '21 at 6:40 pm
One day perhaps but not now please, it's late here!
John_Ru (rep: 6142) Dec 17, '21 at 6:41 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login