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

Issue with adding month

0

I am trying to get my amortization schedule working in real time with current and future figures.

in cell M24 it shows the date in B24 which is correct and shows 16/12/2021

=IF(N24="","",IF(B24<>"",B24,WORKDAY(EOMONTH(O$18,ROW(B24)-6)+7,6)))

however M7 needs to show the date M6+1 month (workday only)

=IF(N25="","",IF(B25<>"",B25,(WORKDAY(EOMONTH(O$18,ROW(B25)-6)+7,6))))

this shows 15/05/2023

So in short I would like the date in column M = the same line in Column B if B has a date otherwise I would like M to show the previous date in M plus 1 month and not landing on a weekend.

so if N24 is blank then blank, otherwise if B 24 is not blank then show the value (date) in B24 otherwise show the previous date (M5) plus 1 month (only days monday to Friday).

so the formula (=WORKDAY(EOMONTH(O$18,ROW(B25)-6)+7,6))) worked before I had it look at column B and works in relation to B but not the first time B is blank.

Sorry John

oh and actual bank Amortization is on TAB bank.

My partner wants to see real time amortization and future.

EDIT

sorry uploaded correct file.

exceldata-analysisfinance
MS Office Version: 2019
posted by: wildecoyote1966 (rep: 30) on Sat Dec 18, 2021 at 11:39 pm
Answer
Discuss

Discussion

Albert

I think you attached the wrong file (e.g. there's no formula in O6 and you have fortnightly payments in column B but monthly ones in column M)- please check and correct.
John_Ru (rep: 6642) Dec 19, '21 at 6:49 am
Still think you uploaded the wrong file (there's a Bank tab but nothing in cell O6 of Sheet1) but will attempt an answer
John_Ru (rep: 6642) Dec 20, '21 at 3:51 am
Sorry John.
I should have changed that to M grr
wildecoyote1966 (rep: 30) Dec 20, '21 at 6:17 pm
Add to Discussion

Answers

0
Selected Answer

Albert

In the revised file attached, the formula in M23 (and cells below) takes the form:

=IF(L23="","",IF(C23="",WORKDAY(EDATE(DATE(YEAR(M22),MONTH(M22),10),1),-1),B23))
where EDATE gives the same day but a number of months after (using DATE to get the serial number for the 10th of the ame month as M22) but I've put WORKDAY around that and used -1 as its second parameter to get the next working day before that - normally that will be the 9th but if it falls on the weekend, it becomes the Friday before (e.g. 07 Jan 2022) so you plan to pay ahead. I've set the cell formatting to "dddd dd/mm/yyyyy" so you see the name of the day in the cell too and the conditional formatting uses:
=IF(ISBLANK(B24),1,0)
to set such a date as italics.

Otherwise, if there's nothing in C22 or L23, the date from B23 appears instead (non-italics).

Note that I added an imaginary payment date in B23 for 27 Dec (to check the dates in column M worked as I expected)- please delete

That does reduce the number of repayments but I guess that's a function of your spreadsheet (which I don't have time to understand!).

Hope this helps.

posted by: John_Ru (rep: 6642) on Mon Dec 20, 2021 at 4:05 am
Discuss

Discussion

Thanks John

I'll try a quick explaination of what I am trying to achieve.

Tha loan takes 1 payment per month only and any others I have to transfer myself. So the amortization table needs to reflect the bank. but Terry wanted it to show real time as well as future. so hence the amortization table needs to change as real time transactions occur.

EDIT
If I add a new date in column B I get the previous issue again with the next date in column M not changing to the middle of the next month but instead being 2 months away.

oh scheduled date is 9th of the month

Thanks again
Albert
wildecoyote1966 (rep: 30) Dec 20, '21 at 6:23 pm
Thanks Albert. Looking a amortisation tables is not my idea of fun but glad it helped and hopefully Terry is pleased. Have fun! 
John_Ru (rep: 6642) Dec 20, '21 at 6:35 pm
Albert. I thought my answer did what you wanted but if not, please Deselect it. It's too late for me to look at it now but I might tomorrow 
John_Ru (rep: 6642) Dec 20, '21 at 6:51 pm
Albert. I took a look at your file again and think your request "So in short I would like the date in column M = the same line in Column B if B has a date otherwise I would like M to show the previous date in M plus 1 month and not landing on a weekend." isn't what you want since  (it seems to me) you might make interim extra payments (NOT on the compulsory payment date around the 9th) e.g. the 16th.

Does the next date in M need to be (estimated at) 9th of the next month (if the previous date is >9th of the current month) but 9th of the current month if not?

Please clarify in your original question.
John_Ru (rep: 6642) Dec 21, '21 at 5:00 am
Forget the above (largely) Albert - please see the revised Answer/file. If that doesn't work I doubt I'll have time to do anything to help.

Hope this does the trick!
John_Ru (rep: 6642) Dec 21, '21 at 4:53 pm
Hi John

It appears to be what I want.  I added a few dates and it does what I need.

just so you know. The bank takes a repayment on the 9th of the month regardless of what other payments have been made.

Thanks again John.
wildecoyote1966 (rep: 30) Dec 21, '21 at 6:33 pm
Glad it worked Albert
John_Ru (rep: 6642) Dec 21, '21 at 7:09 pm
Add to Discussion


Answer the Question

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