CALCULATE YIELD

[apgmin]

If I Deposit $ 250 in a scheme, I get returns as under
Cell value of Year 1 = A1
what is the yield of the scheme

YEAR 1	YEAR 2	YEAR 3	YEAR 4	YEAR 5	YEAR 6	YEAR 7	YEAR 8	YEAR 9	YEAR 10	TOTAL
0	0	0	4.30	28.61	44.52	65.44	100.42	112.12	374.89	730.30

 

[dcardno]

put the cash outflow (-$250) in A1, and then use the formula =IRR(A1:J1) - don't include the total (in column K) - and make sure yoiu have the number of year with no return correct - is your initial deposit in "Year 1" or in Year 0?" assuming it is in Year 1, with two year before the first return, I get 15% yield

 

[joeu2004]

If I Deposit $ 250 in a scheme, I get returns as under [....] what is the yield of the scheme

Unfortunately, your example raises more questions that it answers. The terms "yield" and "return" have different meanings to different people and in different contexts. And it is unclear what the "returns" represent, and in particular what the value in Year 10 represents. It would help if you explained what you invested in (savings, bonds, etc); what the "returns" starting in Year 4 represent (e.g. interest, dividends, etc); and what you did with the "returns" (reinvestsed in the account; or withdrawn from the account; etc). Also, the timing of the initial investment ($250) is unclear.

I assume that you invested $250 at the beginning of Year 1 (or the end of Year 0), and there was no "return" at the end of Year 1.

I also assume that $374.89 at the end of Year 10 represents the entire ending balance, not just the "return" for that year.

In that case, at a minimum, the cash flow model would like column B in the following.

Book1
	A	B	C
1	End year...	Ex 2	Ex 3
2	0	-250.00	-250.00
3	1	0.00	0.00
4	2	0.00	0.00
5	3	0.00	0.00
6	4	4.30	0.00
7	5	28.61	0.00
8	6	44.52	0.00
9	7	65.44	0.00
10	8	100.42	0.00
11	9	112.12	0.00
12	10	374.89	374.89
13	IRR	13.15%	4.13%
Sheet2

I assume that by the term "yield", you mean the IRR, which is calculated in row 13.

(You can see formulas by hovering the cursor over the cell value or by clicking the cell and looking at the Formula Field labeled "fx".)

Note that the IRR is 13.15%, not 15.04% that "dcardno" calculated. The difference is: "dcardno" effectively put the $250 investment at the end of Year 1. I presume that is not your intent.

In any case, both cash flow models (dcardno's and my column B) are incorrect, if the "returns" represent earnings that are reinvested in the account.

In that case, column C is the correct cash flow model to use, and the IRR is 4.13%. The timing of the "returns" that are reinvested have no bearing on the IRR.

That might surprise you. To demonstrate, consider a simple model: we invest $100,000 at the beginning of Year 1, and the investment earns 1% interest compounded in each of years 1 through 5. In that case, the IRR should be 1%.

Book1
	A	B	C	D
1	End year...	Wrong	Better	Correct
2	0	-100,000.00	-100,000.00	-100,000.00
3	1	1,000.00	1,000.00	0.00
4	2	1,020.00	1,010.00	0.00
5	3	1,040.30	1,020.10	0.00
6	4	1,060.90	1,030.30	0.00
7	5	1,081.82	105,101.01	105,101.01
8	IRR	-54.67%	1.80%	1.00%
Sheet1

Column B shows an incorrect model of the cash flows. They are the initial investment in B2 and just the interest earned at the end of each year in B3:B7. Note that the IRR is negative(!).

Column C shows a more-correct model insofar as the last cash flow (B7) is the ending balance, which includes the interest earned in Year 5. The IRR is 1.80%. Close; but it is still wrong.

Column D shows the correct model: the only relevant cash flows are the initial investment (B2) and the ending balance (B7). Compounded interest is not a cash flow because it is not an external amount that is added to the account, and it is not an amount that is withdrawn from the account. Note that the IRR is 1%, as it should be.