Forum

Notifications
Clear all

Excel formula of installment dates of 5 years loan

8 Posts
3 Users
0 Reactions
273 Views
(@salim-gul)
Posts: 33
Trusted Member
Topic starter
 

Dear Sir

 

I want to set a financial formula of semi annual installments dates of 5 year ( the loan of 5 years semi annual return dates first date of installment is suppose 01-01-2016, second 01-07-2016, ........ till last date of installment 01-07-2020 automatically 

 
Posted : 04/03/2020 2:13 am
(@purfleet)
Posts: 412
Reputable Member
 

Like this? (See attached)

You don't mention about bank holidays or if the calculation is every 6 months or based on 360 day/year or 365 day/year.

A straight 6 months is fairly easy

Purfleet

 
Posted : 04/03/2020 6:08 am
(@salim-gul)
Posts: 33
Trusted Member
Topic starter
 

Dear Purfleet 

Thank you for guidance, but the formula which i want, there is only number of years, number of payment in a year and starting date is given all the colored dates set formula automatically 

 
Posted : 04/03/2020 11:41 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello Salim,

Perhaps you are looking for the COUPNCD function?

 
Posted : 05/03/2020 5:52 pm
(@salim-gul)
Posts: 33
Trusted Member
Topic starter
 

Dear Anders Sehlstedt

 

Yes you sir, but COUPNCD Formula or COUPNUM these formulas only show the dates of current year installment, but here i am looking for the formula which show the all dates of installments in 5 year from start date to last date of coupon (installment) 

one thing more i would like to add here, about what i am looking for, which is a formula of loan amortization named sheet in this forum, unfortunately i did not under stand how to formulate it 

 
Posted : 06/03/2020 3:53 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello Salim,

Yes, the COUPNCD function gives you only the next coupon date after the settlement date, so you need to give it a new settlement date for each next coupon date. In attached file you will see what I mean. The end result is just the same as what Purfleet gave you. If you don't like this approach or want to present it differently, just go with whatever solution that works for you, for example using an IF formula to decide what date the settlement date is to be and so on.

The structure in the sample file is this:
Cell C3 = Number of years
Cell C4 = Number of installments per year
Cell C5 = Settlement date (Start)
Cell C6 = Maturity date (End)

In cell C8 I simply show the date in cell C5.
In cell C9 I use the =COUPNCD($C8;$C$6;$C$4), I have then dragged and copied this formula down to cell C17. The range from cell C8 to C17 now shows all the installment dates and as you see, the settlement date per row is the installment date from the row above. To my knowledge there is no other way to see all the dates at once, but if you for example just want to see in one cell when the next installment date is based on today's date, that can be arranged. A bit tricky perhaps, but doable.

For your other question about loan amortization, I assume you are looking for the PMT function. Check out these pages for information about it.
https://www.myonlinetraininghub.com/excel-functions/excel-pmt-function

https://support.office.com/en-us/article/pmt-function-0214da64-9a63-4996-bc20-214433fa6441?NS=EXCEL&Version=90&SysLcid=1033&UiLcid=1033&AppVer=ZXL900&HelpId=xlmain11.chm60107&ui=en-US&rs=en-US&ad=US

Sometimes I find a good help in using a ready made template to better understand how to use the functions.
https://templates.office.com/en-US/Search/results?query=amortization

I hope I was able to give you some help.

Br,
Anders

 
Posted : 06/03/2020 4:53 pm
(@salim-gul)
Posts: 33
Trusted Member
Topic starter
 

Dear Anders Sehlstedt 

you have solved my problem, i was looking for that one, the formula you have posted is very useful not only for me but every finance student, again i am thankful you for give me your expensive time 

 
Posted : 07/03/2020 12:05 pm
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello Salim,

Thank you for your nice feedback.

 
Posted : 07/03/2020 6:17 pm
Share: