+ Reply to Thread
Results 1 to 6 of 6

Excel help - portfolio rebalancing

  1. #1
    Registered User
    Join Date
    03-29-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Excel help - portfolio rebalancing

    Hi Excel Forum,

    I'm having a bit of difficulty with a problem in Excel and was hoping that some of you might be able to help. I have daily return data for 6 securities where I have calculated rolling betas and alphas for each previous 5 trading days.

    I want to create a simple portfolio that is long the 3 lowest beta securities while being short the highest 3 beta securities. The portfolio will be rebalanced every calendar month to ensure that the 3 lowest beta securities are in the "low-beta" portfolio and the 3 highest beta securities are placed in the "high-beta" portfolio.

    As the betas of the underlying securities change over time, so does the rankings of the betas and therefore the formations of the "low-beta" and "high-beta" portfolios. I'm having trouble figuring out how to do the monthly rebalancing of the portfolio the easiest way and I was wondering if any of you had any suggestions on how to do this?

    Thanks in advance and please let me know if you need any additional info.

  2. #2
    Forum Contributor
    Join Date
    09-23-2008
    Location
    UK
    Posts
    137

    Re: Excel help - portfolio rebalancing

    Only 6 securities? That's a small universe!?

    Since you want the 3 highest and 3 lowest, the obvious thing would be to sort your security list by their betas. From the toolbar: data>sort. Or you could make use of the rank function.

    It might be clearer what you are struggling with if you share a sample file.

  3. #3
    Registered User
    Join Date
    03-29-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel help - portfolio rebalancing

    Hi firefly2k8,

    Thank you very much for your reply. It's actually 11 different bond indices, but I figured that if I got the general gist of it, I should be able to expand to the 11 indices quite easily.
    I have attached the excel file here: http://www.2shared.com/file/QJ-pLekj/DFI.html

    I have manually calculated the first few months of the portfolio formations in the Factors tab and rebalanced it manually each calendar month, manually looking at the betas to rebalance the low-beta and high-beta portfolios. I'm looking for an easier way to do this, rather than manually rebalancing by looking at the betas for each calendar month to determine which portfolio the security belongs to.

    Many thanks, please don't hesitate if you have any other questions.

  4. #4
    Registered User
    Join Date
    03-29-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel help - portfolio rebalancing

    Bump, I would very much appreciate if anyone is able to help me
    Last edited by Domino; 04-02-2012 at 12:51 PM.

  5. #5
    Forum Contributor
    Join Date
    09-23-2008
    Location
    UK
    Posts
    137

    Re: Excel help - portfolio rebalancing

    sry couldn't open you file.

  6. #6
    Registered User
    Join Date
    03-29-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel help - portfolio rebalancing


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1