Hi,
I have 100 bonds in a portfolio and I have their maturity dates, next coupon date and the frequency of the coupons.
I need to create a formula (or VBA?) that can be applied to different bonds so it maps the coupons from the next one up until maturity.
So far I have been using EDATE(Previous Coupon Date, 12/Frequency) but this is proving quite manual as I have to do it one by one.
I have attached an example of 2 bonds I am looking at.Coupon Schedule.xlsx
Thanks
There are no attempted formulae in your attachment. What is the EXACT formula you have been using so far?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi, there is for coupons 2-6. Used the Edate(Previous Coupon,12/frequency)
That's not the formula - its a description of it. Please paste the EXACT formula into your next response (it should have been in the sample workbook for us to refer to).
=EDATE(C3,12/E3)Screenshot 2022-09-22 at 11.26.41.pngCoupon Schedule.xlsx
Formula is in c3-c8 and c11-c15
Thanks - there is NO formula in the version I opened earlier!!!
I'll have a look at this one.
OK - this is not straightforward.
It is not a good idea to have manually entered data and formulae in the same column, so how can we overcome this?
Could have a mapping table for the security which contains the maturity date, next coupon date, payment frequency and coupons remaining? I have attached an exampleCoupon Schedule (1).xlsx
Sorry - I'm not getting it at all. Someone else will get it, I am sure.
What do you think the solution is?
I don't know - I don't get what you are trying to do. You need to wait for someone else to have a look.
Ok not to worry, thanks for your help
output on Sheet3
Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Hi John,
That's perfect, just what I wanted!
I tried to adapt it into my workflow but got lost where you defined 'j', I'm a noob when it comes to VBA.
I have done away with worksheet 1 and now just have my 'bond schedule' tab and 'coupon timeline' tab.
How would I adapt the macro so that I could make 'coupon timeline' from my 'bond schedule' tab. Its a big askCoupon Schedule Update.xlsm
Please Login or Register to view this content.
Last edited by JohnTopley; 09-22-2022 at 03:25 PM.
Wonderful, thanks for your continued support. I seem to get 'Run time error 1004' Unable to get Edate property of worksheet function class in my workbook...
Try
Please Login or Register to view this content.
No joy im afraid, I have attached my workbook for your considerationMacro.xlsm
Your dates are TEXT not Excel dates.
John, you are a star - thank you ever so much for all your patience. Its much appreciated.
Thank you!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks