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.
