SUBSCRIBE NEWSLETTER
  • Change Language
  • English
  • Hindi
  • Marathi
  • Gujarati
  • Punjabi
  • Tamil
  • Telugu
  • Bengali
  • Tutorials 5 excel functions that advisors must know

    5 excel functions that advisors must know

    Complete your client’s complex financial calculations at the click of a button.
    Shreeta Rege Apr 27, 2018

    Hi, I am your friend Excel. Be they engineers or accountants, I am their go-to guy for simplifying calculations and making work less tedious.

    I hear you wondering, "Can Excel help me in my work too?" Fret not advisors. I, Mr Excel will share some functions, which will greatly help you when you give your clients financial advice.

    1) Return on lumpsum investment

    You can easily compute returns generated on a client’s investment using XIRR function whenever a client’s portfolio has generated positive returns.

    For e.g.

    • Date of first investment: 01/01/2000
    • Date of second investment: 02/02/2005
    • Amount invested on 01/01/2000: Rs.500 (amount invested is always negative as it is the money given by the investor)
    • Amount invested on 02/02/2005: Rs.500
    • Current date: 25/04/2018
    • Current value of investment: Rs. 15000 (current value is always positive as it is the money which the investor receives)

    The guess value can be left blank.

    2) Return on SIP investment

    XIRR function is also very useful for computing positive returns generated on a client’s SIP investment

    For e.g.

    • Date of initial investment: 01/01/2017
    • SIP date 1st of every month
    • Amount invested every month: Rs.1,000 (amount invested is always negative as it is the money given by the investor)
    • Current date: 25/04/2018
    • Current value of investment: Rs. 25,000 (current value is always positive as it is the money which the investor receives)

    Let us first calculate current value of SIP investments.

    Current Value = Current NAV * Number of units held by the investor

    Calculating SIP returns using XIRR function

    The guess value can be left blank.

    3) Effective rate of return

    You may have seen that bank FDs quote a rate say 7%. However, compounding means that your investments will grow at a rate higher than the quoted rate.

    You can calculate the effective rate of interest by using the EFFECT formula in excel.

    For e.g.

    • Annual interest rate offered (nominal_rate): 7%
    • Compounding frequency (npery): Quarterly. Therefore 4 compounding per year

     4) Future value of investment

    This is an extremely useful tool to highlight the importance of investing. It makes clients aware that cost of their goals increases due to inflation.

    The future value is negative as it is an outflow at a future date.

    The type is considered as ‘0’ as the money will be required at the end of the period

     

    Alternatively, this tool can be used to understand the value of your investment at a future date.

    The present value is negative as it is the money given by the investor.

    The type is considered as ‘0’ as the money will be required at the end of the period

    5) Systematic Withdrawals using PMT function

    The PMT function is useful as an indicative tool for projecting the maximum amount that an investor can withdraw every year from his existing investments to fund his retirement.

    Simply put, you can advise your clients to treat their existing mutual fund investment as a kitty from which they can do periodic withdrawals for their post retirement expenses.

    The present value is negative as it is the money given by the investor.

    The future value is 0 as at the end of the period you do not want any investment balance.

    The type is considered as ‘1’ as the money will be withdrawn at the start of the period

    Use these excel tools to assist your clients in their financial needs.

     

     

    Have a query or a doubt?
    Need a clarification or more information on an issue?
    Cafemutual welcomes all mutual fund and insurance related questions. So write in to us at newsdesk@cafemutual.com

    Click to clap
    Disclaimer: Cafemutual is an industry platform of mutual fund professionals. Our visitors are requested to maintain the decorum of the platform when expressing their thoughts and commenting on articles. Viewers are advised to refrain from making defamatory allegations against individuals. Those making abusive language or defamatory allegations will be blocked from accessing the web site.
    7 Comments
    PREMANSHU DAS · 5 years ago `
    Really great. Thank U
    Umang Tailor · 4 years ago
    Your Excel Work Is Very Good.
    Reply
    Ravi Bandekar · 5 years ago `
    This is wonderful post! There are so many online/offline auto calculators for this, but essentially any advisor should (or at least figure out for himself) explain the returns generated, and this will be a great help. I had at a times tried to work this out in excel, but got confused by the terms that were foreign to me. So Congratulations and Thanks!!
    amarish shah · 5 years ago `
    excellent
    if i make sip of rs. 2000 for 20 years and if the yield is 15% cagr and the commission is 1.00% what will be my commision during the 20 years [yto y basis] this will be useful to all distributors for their future target
    jegadeeswaran · 5 years ago
    https://www.rrfinance.com/MFund/SIP_Revenue_Calculater.aspx .


    Sir , try in this link . You would get some thoughts .
    Reply
    Arun Sharma · 5 years ago `
    info
    SUPRIYA JHA · 3 years ago `
    VERY USEFUL FUNCTIONS.. THANKS FOR SHARING. WOULD LIKE TO LEARN MS EXCEL MORE.
    Login or Sign up to post comments.
    More than 2,07,000 of your industry peers are staying on top of their game by receiving daily tips, ideas and articles on growth strategies. Join them and stay updated by subscribing to Cafemutual newsletters.

    Fill in the below details or write to newsdesk@cafemutual.com and subscribe to Cafemutual Newsletter now.