Looking at ways to enhance your business & career? Log onto streetsahead.in to upgrade yourself!
SUBSCRIBE NEWSLETTER
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.

 

 

Click to clap
3 Comments
PREMANSHU DAS · 4 months ago
Really great. Thank U
Ravi Bandekar · 4 months 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 · 4 months 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
Wish to stay on top of your game? Get daily tips, ideas and articles to grow your business.
Subscribe to Cafemutual Newsletter.