+ Reply to Thread
Results 1 to 20 of 20

Mapping Coupon Payments as a list for different bonds

  1. 09-22-2022, 06:08 AM #1
    jaiib
    jaiib is offline
    Registered User
    Join Date
    09-20-2022
    Location
    uk
    MS-Off Ver
    2016
    Posts
    20

    Mapping Coupon Payments as a list for different bonds

    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
    Reply With QuoteRegister To Reply
  2. 09-22-2022, 06:12 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,244

    Re: Mapping Coupon Payments as a list for different bonds

    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 09-22-2022, 06:18 AM #3
    jaiib
    jaiib is offline
    Registered User
    Join Date
    09-20-2022
    Location
    uk
    MS-Off Ver
    2016
    Posts
    20

    Re: Mapping Coupon Payments as a list for different bonds

    Hi, there is for coupons 2-6. Used the Edate(Previous Coupon,12/frequency)
    Reply With QuoteRegister To Reply
  4. 09-22-2022, 06:25 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,244

    Re: Mapping Coupon Payments as a list for different bonds

    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).
    Reply With QuoteRegister To Reply
  5. 09-22-2022, 06:29 AM #5
    jaiib
    jaiib is offline
    Registered User
    Join Date
    09-20-2022
    Location
    uk
    MS-Off Ver
    2016
    Posts
    20

    Re: Mapping Coupon Payments as a list for different bonds

    =EDATE(C3,12/E3)Screenshot 2022-09-22 at 11.26.41.pngCoupon Schedule.xlsx

    Formula is in c3-c8 and c11-c15
    Reply With QuoteRegister To Reply
  6. 09-22-2022, 06:31 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,244

    Re: Mapping Coupon Payments as a list for different bonds

    Thanks - there is NO formula in the version I opened earlier!!!

    I'll have a look at this one.
    Reply With QuoteRegister To Reply
  7. 09-22-2022, 06:33 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,244

    Re: Mapping Coupon Payments as a list for different bonds

    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?
    Reply With QuoteRegister To Reply
  8. 09-22-2022, 06:44 AM #8
    jaiib
    jaiib is offline
    Registered User
    Join Date
    09-20-2022
    Location
    uk
    MS-Off Ver
    2016
    Posts
    20

    Re: Mapping Coupon Payments as a list for different bonds

    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
    Reply With QuoteRegister To Reply
  9. 09-22-2022, 06:48 AM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,244

    Re: Mapping Coupon Payments as a list for different bonds

    Sorry - I'm not getting it at all. Someone else will get it, I am sure.
    Reply With QuoteRegister To Reply
  10. 09-22-2022, 06:49 AM #10
    jaiib
    jaiib is offline
    Registered User
    Join Date
    09-20-2022
    Location
    uk
    MS-Off Ver
    2016
    Posts
    20

    Re: Mapping Coupon Payments as a list for different bonds

    What do you think the solution is?
    Reply With QuoteRegister To Reply
  11. 09-22-2022, 06:51 AM #11
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,244

    Re: Mapping Coupon Payments as a list for different bonds

    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.
    Reply With QuoteRegister To Reply
  12. 09-22-2022, 06:55 AM #12
    jaiib
    jaiib is offline
    Registered User
    Join Date
    09-20-2022
    Location
    uk
    MS-Off Ver
    2016
    Posts
    20

    Re: Mapping Coupon Payments as a list for different bonds

    Ok not to worry, thanks for your help
    Reply With QuoteRegister To Reply
  13. 09-22-2022, 09:21 AM #13
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,133

    Re: Mapping Coupon Payments as a list for different bonds 

    Please Login or Register  to view this content.
    output on Sheet3
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  14. 09-22-2022, 12:00 PM #14
    jaiib
    jaiib is offline
    Registered User
    Join Date
    09-20-2022
    Location
    uk
    MS-Off Ver
    2016
    Posts
    20

    Re: Mapping Coupon Payments as a list for different bonds

    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
    Reply With QuoteRegister To Reply
  15. 09-22-2022, 12:19 PM #15
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,133

    Re: Mapping Coupon Payments as a list for different bonds 

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JohnTopley; 09-22-2022 at 03:25 PM.
    Reply With QuoteRegister To Reply
  16. 09-22-2022, 01:07 PM #16
    jaiib
    jaiib is offline
    Registered User
    Join Date
    09-20-2022
    Location
    uk
    MS-Off Ver
    2016
    Posts
    20

    Re: Mapping Coupon Payments as a list for different bonds

    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...
    Reply With QuoteRegister To Reply
  17. 09-22-2022, 03:26 PM #17
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,133

    Re: Mapping Coupon Payments as a list for different bonds

    Try

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  18. 09-22-2022, 04:03 PM #18
    jaiib
    jaiib is offline
    Registered User
    Join Date
    09-20-2022
    Location
    uk
    MS-Off Ver
    2016
    Posts
    20

    Re: Mapping Coupon Payments as a list for different bonds

    No joy im afraid, I have attached my workbook for your considerationMacro.xlsm
    Reply With QuoteRegister To Reply
  19. 09-22-2022, 05:27 PM #19
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,133

    Re: Mapping Coupon Payments as a list for different bonds

    Your dates are TEXT not Excel dates.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  20. 09-22-2022, 05:46 PM #20
    jaiib
    jaiib is offline
    Registered User
    Join Date
    09-20-2022
    Location
    uk
    MS-Off Ver
    2016
    Posts
    20

    Re: Mapping Coupon Payments as a list for different bonds

    John, you are a star - thank you ever so much for all your patience. Its much appreciated.

    Thank you!!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. YIELD() function: What assumptions are used for reinvestment of coupon payments?
    By MrBiggy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-25-2022, 03:13 AM
  2. Simple Formulas to list ALL Next payments due (Closest Date to Today) from a list
    By emadmassoud in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-19-2022, 04:22 AM
  3. Mapping cell contents from a list
    By San75 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2016, 05:15 AM
  4. Mapping list
    By john_prince in forum Excel General
    Replies: 3
    Last Post: 12-13-2010, 05:42 PM
  5. Max for 2 list and then mapping to Letters
    By x-plicit in forum Excel General
    Replies: 4
    Last Post: 07-27-2010, 10:38 AM
  6. Mapping incomplete list to a complete list
    By Ctrl Alt Sweet in forum Excel General
    Replies: 1
    Last Post: 11-21-2006, 05:54 AM
  7. Calculating Yield on Multi Coupon Bonds
    By tim4682 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2005, 05:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1