Log In
Register
Home
December 24, 2018
Offline
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
See Answer
Trusted Members
December 20, 2019
Offline
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
December 24, 2018
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
Hello Salim,
Perhaps you are looking for the COUPNCD function?
December 24, 2018
Offline
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
Offline
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:
PurfleetDecember 24, 2018
Offline
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
1 Guest(s)
