0

i am new in finance and want to know not only how to calculate financial parameters using formulas,but underlined idea as well, for instance , let us consider following problem :

A has invested US $100 in 2016. The payment has been made yearly. The interest rate is 10% p.a. What would be the FV in 2019?

i know that formula for future value compounded yearly given present value and interest rate after $n$ years is given by

$ F=p*(1+r)^n $

in our case 

p=-100

r=10% 

n=3

formulas says that

enter image description here

but it should be =FV(10%,3,0,-100,0) yes?what does payment yearly means? first of all let us consider mathematically

so i have invested 100 dollar for three year yes at 10% interest rate, below there is table which shows years and given interest which i will get

enter image description here

formula is given by enter image description here

so where i am making mistake? what does means payment has been made yearly ?

dato datuashvili
  • 9,194
  • i really need help please – dato datuashvili Nov 11 '17 at 19:33
  • "pmt" is confusing. That, I believe, is for cash out. By putting $1$ there you are indicating that you remove a dollar (or some unit) each period...I assume you did not intend to do that. I think you mean to enter $0$ there. – lulu Nov 11 '17 at 19:52
  • sorry i did not understand what did you ask me, could you repeat please – dato datuashvili Nov 11 '17 at 19:54
  • i took it from one site, i copied screenshot from them – dato datuashvili Nov 11 '17 at 19:55
  • I did not ask you anything. I assume you are trying to tie out to your $$133.1$. To do so, set "pmt" to $0$. – lulu Nov 11 '17 at 19:56
  • General note: I try to avoid using "canned" financial functions as they almost always involve unintuitive definitions. I just write my own. That becomes difficult if you get into the details (exact day counts, conventions for holidays, conventions for leap years, and such) but for the most part it works better to write your own. – lulu Nov 11 '17 at 19:58
  • thanks in advance , i respect your advice, i am teacher of excel and i would like to explain them basic ideas, of course for myself i never trust completely to the existed methods – dato datuashvili Nov 11 '17 at 20:08
  • Ok, well as I say I believe entering $1$ under "pmt" means that you extract a dollar at each payment period (imagine that you need some of the coupon as income). So you should get something like three dollars less than $$133.10$ (a bit more than three dollars as you lose the benefit of compound interest on the withdrawn sum). – lulu Nov 11 '17 at 20:10
  • but then question is : why might i want to extract a dollar? what is the purpose? – dato datuashvili Nov 11 '17 at 20:14
  • As I mentioned, people withdraw funds from interest bearing accounts because they need the income. But...it's really not for me, or anyone here, to defend the Excel functions. Maybe there is a site devoted to the Excel function library. – lulu Nov 11 '17 at 20:52

1 Answers1

1

Imagine that you have $P_0=\$\, 100$ on a bank account at $i=10\%$ interest.

After one year you will have $P_1=P_0(1+i)=\$\,110$, after two year $P_2=P_1(1+i)=P_0(1+i)^2$ and so on. So for $n$ years you will have $$P_n=P_0(1+i)^n$$

If you withdraw money every year, for example $W$, you will have \begin{align*} n=0 &\qquad P_0=100\\ n=1 &\qquad P_1=(P_0-W)(1+i)=P_0(1+i)-W(1+i)\\ n=2 &\qquad P_2=(P_1-W)(1+i)=P_1(1+i)-W(1+i)=P_0(1+i)-W(1+i)^2-W(1+i)\\ \ldots\\ \end{align*} So at year $n$ you will have $$ P_n=P_0(1+i)^n-W\sum_{k=1}^n(1+i)^n=P_0(1+i)^n-W\frac{(1+i)^n-1}{i} $$ that is the total future vale is the future value of $P_0$ minus the future value of the stream of withdrawals.

If you instead deposit the value $D$ every year you will have, putting $D=-W$ $$ P_n=P_0(1+i)^n+D\sum_{k=1}^n(1+i)^n=P_0(1+i)^n+D\frac{(1+i)^n-1}{i} $$ that is the total future vale is the future value of $P_0$ plus the future value of the stream of deposits.

In excel funtion $\mathtt{FV(rate,nper,pmt,[pv],[type])}$, we have

  • $i=\mathtt{rate}$
  • $n=\mathtt{nper}$
  • $W,\,D=\mathtt{pmt}$
  • $P_0=\mathtt{pv}$

Pay attention on the sign in excel: a deposit has the minus sign, whereas a withdrawal has the plus sign.

alexjo
  • 14,976