Compound Interest

Graeme155 · Jan 28, 2014

I am trying to calculate the returns on shares, including dividends. If I calculate the share price now and expected in 5 years, this gives me the $ gain. To this I want to add the share dividends ... say 3 cents per share, increasing at 20% p.a.

Thus ($share price 5 years - $share price today)+(3cents*1.20)+(3cents*1.20*1.20)+(3cents*1.20*1.20*1.20)+(3cents*1.20*1.20*1.20*1.20)+(3cents*1.20*1.20*1.20*1.20*1.20) = total $ gain

Calculate from this the return ... total $ gain/today's share price as a % = total % gain over 5 years.

From this I want to calculate the Annual Compound Interest Rate earned.

Hope this is clear

Graeme

Graeme155 · Jan 28, 2014

Hi

Obviously this was a bit long winded .... can anyone advise me how to calculate a compound Annual Interest Rate where the return was 60% over 5 years?

Graeme

mfexcel · Jan 28, 2014

Graeme155 said:
Hi

Obviously this was a bit long winded .... can anyone advise me how to calculate a compound Annual Interest Rate where the return was 60% over 5 years?

Graeme

Try the Function FV

below is from Excel Help

FV(rate,nper,pmt,[pv],[type])For a more complete description of the arguments in FV and for more information on annuity functions, see PV.
The FV function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Rate Required. The interest rate per period.
[*]Nper Required. The total number of payment periods in an annuity.
[*]Pmt Required. The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.
[*]Pv Optional. The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
[*]Type Optional. The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

[TABLE="class: collapse"]
<tbody>[TR="class: trbgeven"]
[TH]Set type equal to[/TH]
[TH]If payments are due[/TH]
[/TR]
[TR="class: trbgodd"]
[TD]0[/TD]
[TD]At the end of the period[/TD]
[/TR]
[TR="class: trbgeven"]
[TD]1[/TD]
[TD]At the beginning of the period[/TD]
[/TR]
</tbody>[/TABLE]

[h=2]Remarks[/h]

Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend checks, is represented by positive numbers.

AhoyNC · Jan 28, 2014

I think this may be what you are looking for.

Formula to calculate rate

Code:

[TABLE="width: 382"]
<colgroup><col width="382"></colgroup>[TR]
   [TD="width: 382"]rate=[(Future Value   / Present Value)^(1/periods)]-1[/TD]
 [/TR]
[/TABLE]

Excel Workbook

A

B

C

D

1

Present Value

25

2

Fruture Value

40

3

Periods

5

years

4

5

Rate

0.098561

6

Sheet

KarieGeiger · May 27, 2014

Everyone doesn’t have to be mathematician just to manage their finances properly. As long as you know how to spend your money wisely you can avoid huge debt and other money troubles. Calculating every penny you spend can show you where and how it goes. Look no further, all the information and facts you will need is at MatchFinancial.com!

Compound Interest

Graeme155

New Member

Excel Facts

Graeme155

New Member

mfexcel

Well-known Member

AhoyNC

Well-known Member

KarieGeiger

New Member

Similar threads

Forum statistics

Share this page

Compound Interest

Graeme155

New Member

Excel Facts

Graeme155

New Member

mfexcel

Well-known Member

AhoyNC

Well-known Member

KarieGeiger

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock