SOLVED

Building dynamic date for Loan Amortization Scheduler

Copper Contributor

Hi,

I am using excel 365 and trying to build Loan Amortization Scheduler. I am trying to get date as dynamically such as Yearly, Half Yearly, Quarterly, Monthly, Bi weekly and even weekly.

 

My question is that If I select frequency of payment like Yearly then date in loan amortization sheet should be reflecting Yearly wise. 

If I select frequency of payment like Monthly then it should be reflecting monthly wise and if I select bi weekly wise frequency of payment then it should be working in date column as bi weekly date. 

 

I am attaching a screen shot for your reference. I want date in column B starting rows from 14 in a sheet.

I hope you must have understood my question what I want here.

 

Aamir1990_0-1632551392434.png

 

It would be greatly appreciated if you could advise on this.

Thanks 

 

2 Replies
which function do I have to use for getting the date result ?
best response confirmed by Aamir1990 (Copper Contributor)
Solution

@Aamir1990 

You will need two alternative formulas, depending on whether the frequency is an exact multiple of days or months,

= startDate + SEQUENCE(nper,1,0,frequencyDays)

= EDATE(startDate, SEQUENCE(nper,1,0,FrequencyMonths))
1 best response

Accepted Solutions
best response confirmed by Aamir1990 (Copper Contributor)
Solution

@Aamir1990 

You will need two alternative formulas, depending on whether the frequency is an exact multiple of days or months,

= startDate + SEQUENCE(nper,1,0,frequencyDays)

= EDATE(startDate, SEQUENCE(nper,1,0,FrequencyMonths))

View solution in original post