

December 24, 2018

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


Trusted Members

December 20, 2019


VIP

Trusted Members

December 7, 2016

Hello Salim,
Perhaps you are looking for the COUPNCD function?


December 24, 2018

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

VIP

Trusted Members

December 7, 2016

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.myonlinetraininghu.....t-function
https://support.office.com/en-.....#038;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/e.....ortization
I hope I was able to give you some help.
Br,
Anders

Answers Post
The following users say thank you to Anders Sehlstedt for this useful post:
Purfleet1 Guest(s)
