TIME VALUE OF MONEY (TVM) (Part 2) - WHAT FINANCIAL ADVISORS MUST KNOW

Content: Welcome to the second part of the series on time value of money. Just to rewind, in the last article we had introduced how to calculate the effective rate for any given period given any rate for a different time frame. We had were introduced to the basic terms used in excel /spreadsheet functions which we are again repeating in this piece. We also saw how we could use FV function for calculating future value of lumpsums and this is from were we will pursue our journey further...

Primary Sources of Change:

Basic Excel Function Terms: FV = Future value. PV = Present value. Rate = Interest rate per period. NPer = Number of periods (payment period). Pmt = Regular Payment made “per period”. Type = Due date for payments where '1' means due at the beginning of a period and '0' (default) means due at the end of the period.

<p">The PV Calculations (Lumpsum)

The present value calculations are important when you want to know the value of any future amount (either lumpsum or annuity) in today terms.

Client question 1: I want to become a crorepati in 10 years. How much should I save today?
Excel Function: PV(rate,nper,pmt,fv,type) since we want to know the investment needed today.
Inputs: Investments returns or rate is assumed at 15%. We will be using the fv value here since the future value is already known. Since it is already a yearly problem, we will go ahead with nper as 10 and rate as 15%.
Solving Function: PV(rate,nper,pmt,fv,type) = PV(15%,10,0,-10000000,1) = Rs.24,71,847/-
Formula: I = FV / (1+r)^ n where I is the lumpsum investment needed today, n is the number of periods and r is the rate of returns = 10000000 / (1+15%) ^ 10 = 24,71,847

The Rate Calculations (Lumpsum)

Rate can mean any rate of increase or decrease in a problem. It can be investment returns, interest rates, inflation or deflation. We need to find ates in circumstances were we are aware of an required amount and period but do not know the rate which will be required to bridge the gag.

Client question 1: I have Rs.500,000 today which I wish to double in 5 years. How much returns should I earn to make this happen?
Excel Function: Rate (nper,pmt,pv,fv,type,guess) since we the period and the starting and end amounts but not the rate.
Inputs: pv = -500,000 since it will be an outflow and fv = 10,00,000 after 5 years. There is no recurring payment so pmt is '0' and type we assume to be 1 for beginning period calculations. Guess of '0' works very often and even if we leave it blank, the excel will take it as '0' default.
Solving Function: Rate (nper,pmt,pv,fv,type,guess) = Rate(5,0,-500000,1000000,1) = 14.87%. The client will have to invest in an asset class that will possibly give Rs.14.87% returns in 5 years. We skipped writing guess as excel assumes it as '0'.

Annuities:

Let us first understand what an annuity is. An annuity is simply a series of payments occurring for a defined period. There are many applications of annuities for financial advisors and it is something very commonly found while planning investments and withdrawals which do not happen in lumpsums but installments. Yes, you guessed it right. An SIP is an annuity that you are saving while a pension is an annuity that you are withdrawing.

When while with annuities or in other words, series of payments, we will enter the annuity value as “pmt” in our excel functions. Note that for lumpsum or one time calculations, we were entering values as pv or fv. Calculating for annuities in formulas is not simple so we deliberately intend to skip them since excel functions can easily do that boring job for us.

The FV calculations (Annuity)

This function is most commonly used find the future value of annuities with or without beginning investment. It can be effectively used to choose between different annuity options or between a lumpsum or an annuity payment option for a defined period and rate.

Client question: I plan to save Rs.5,000 p.m. in an equity SIP. How much will I get after 5 years? Excel Function: FV(rate,nper,pmt,pv,type) since we want to know the end value in future. Inputs: Investments returns or rate is assumed at 12% conservatively and the SIP is beginning today (annuity due = type 1). Since, it is a monthly investment, we will calculate monthly figures for period and rate. Thus, nper of 5 years is 60 months and we now we need to change the 12% effective annual returns to monthly effective returns. It is not 12% /12 as we read in last article. Try to find that yourself before you read ahead.
Note that we also have to use pmt here since it is periodic payment talking here. We will not use any value for pv since there is no accumulated wealth /lumpsum amount given here. Had it been a lumpsum problem or any starting amount was given, we would have used that figure in pv.
Solving Function: FV(rate,nper,pmt,pv,type) = FV(0.949%,60,-5000,,1) = Rs.4,05,518/-

The PV Calculations (Annuity)

This function can be effectively used to find money needed to fund an annuity in future or to basically decide between multiple annuity options with differing periods and/or annuity amounts. This function can be also effectively used to decide between a lumpsum today or an annuity in future. This decision can be very important as your client can potentially make more money with a lumpsum today than a deferred payments in future.

Client question 1: I have just retired and I wish plan for a monthly withdrawal of Rs.5,000 every month from my investments for the next 20 years. How much should I invest?
Excel Function: PV(rate,nper,pmt,fv,type) since we want to know the investment needed today for an annuity.
Inputs: Investments returns or rate is assumed at 8% for a conservative investment. Since the time period is considered as monthly, we will take the monthly effective rate of 8% which is 0.64%; nper is 20*12 and pmt is -5000 since it is an annuity withdrawal, fv is '0' and we will assume it to be ordinary annuity (withdrawals at each period end).
Solving Function: PV(rate,nper,pmt,fv,type) = PV(0.64%,240,-5000) = Rs.6,10,389/- is the investment required to made today in an asset class with returns of 8% p.a. Note, we skipped entering values for fv and type as they are '0' and excel considers them as default '0' when missing.

The Rate Calculations (Annuity)

Rate function is also most commonly used for loans where we need to find the effective rate of interest with an EMI. It can also be effectively applied to choose between different investment or loan repayment options. For rate calculations, one needs to be careful as the results are for the 'period' considered and we may need to convert it into annual rate if the used period in the function differs.

Client question 2: I have borrowed Rs.1,0,000 today from my bank for purchase of a bike. The loan is for 1 year and I need to pay an EMIs Rs.9,000. What is the interest rate charged?
Solving Function: Rate (nper,pmt,pv,fv,type,guess) = Rate (12,-9000,100000,0,1,0) = 1.43% monthly. Converting this to annual will give us 18.59% as the effective annual interest rate charged by the bank.

We can also use Rate to find the required returns on an SIP, just like lumpsum, for a target value. Client question 2: I wish to accumulate Rs.10 lakhs in 5 years by saving Rs.10,000 monthly. What will be estimated returns required on this?
Inputs: We are quoting all figures in monthly standard here. SIP = Pmt = -10,000. FV = 10,00,000, Nper = 60 months.
Solving Function: Rate (nper,pmt,pv,fv,type,guess) = Rate(60,-10000,0,1000000,1,0) = 1.57%. Since we were talking of monthly standard, converting this to effective annual rate will give us the figure of (1+1.57%)^12 -1 = 20.54% required annual returns.

Friends, we started this series in order to help you get more familiar with the excel functions which can be effectively used in our day-to-day practice. We have assumed that you are familiar with the basics of excel and if not, we urge you to get familiar as it is something basic to being a financial advisor. We will continue this series with the hope and belief that you are practicing these problems yourself and getting better and better at it each day.

Functions covered till now...

{s}
[[script type="text/javascript"]]
$(document).ready(function(){
new DiscussionBoard("divDiscussionBoard", "98", "http://www.njwebnest.in/esaathi/index.php/discussion").load();
});
[[/script]]
{/s}