Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Valuation Models Worksheet: Calculating NPV and Rate of Return, Lecture notes of Finance

University of StrathclydeFinance

This worksheet provides a step-by-step guide to calculating net present value (npv) and rate of return using two valuation models: dividend model and earnings model. Users are required to input initial earnings, retention rates, and discount rates for the given years. The worksheet also includes an example of ramon plc for illustration.

Typology: Lecture notes

2010/2011

Uploaded on 09/10/2011

juno
juno 🇬🇧

4.8

(10)

314 documents

1 / 10

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
WORKSHEET - VALUATION MODELS
DATA REQUIRED
YEAR 1 2 3 4
INITIAL EARNINGS 6
RETENTION RATE 0,7 0,45 0,3 0,25
0,4 0,3 0,2 0,1
DISCOUNT RATE 0,1
Year Earnings
Retention
Dividend
Retention Rate of NPV
Ratio Return
1 6,00 0,7 1,80 4,20 0,4 12,60
2 7,68 0,45 4,22 3,46 0,3 6,91
3 8,72 0,3 6,10 2,62 0,2 2,62
4 9,24 0,25 6,93 2,31 0,1 0,00
DIVIDEND MODEL
YEAR
Dividends
PVF Present Value
1 1,80 0,9091 1,6364
2 4,22 0,8264 3,4909
3 6,10 0,7513 4,5843
3 92,40 0,7513 69,4200
79,1317
EARNINGS MODEL
YEAR Earnings NPVs PVF Present Value
1 6,00 10,0000 60,0000
1 12,60 0,9091 11,4545
2 6,91 0,8264 5,7124
3 2,62 0,7513 1,9647
79,1317
YEAR Earnings
Dividends
1 6,0000 1,8000 1,6364
2 7,6800 4,2240 5,1273
3 8,7168 6,1018 9,7116
4 9,2398 6,9299 14,4448
5 9,4708 7,1031 18,8553
6 9,7076 7,2807 22,9650
7 9,9503 7,4627 26,7946
RATE OF RETURN ON
INVESTMENT
Terminal
Value
Present
Value of
Cumulative
Dividends
1234567891
0
1
1
1
2
1
3
1
4
1
5
1
6
1
7
1
8
1
9
2
0
2
1
2
2
2
3
2
4
2
5
2
6
2
7
2
8
2
9
3
0
3
1
3
2
3
3
3
4
3
5
3
6
3
7
3
8
3
9
4
0
4
1
4
2
4
3
4
4
4
5
4
6
4
7
4
8
4
9
5
0
5
1
5
2
5
3
5
4
5
5
5
6
5
7
5
8
5
9
6
0
0,0000
5,0000
10,0000
15,0000
20,0000
25,0000
30,0000
Growth of Dividends
Ye ars
Dividends
Change the values -
outcomes should
change
pf3
pf4
pf5
pf8
pf9
pfa

Partial preview of the text

Download Valuation Models Worksheet: Calculating NPV and Rate of Return and more Lecture notes Finance in PDF only on Docsity!

WORKSHEET - VALUATION MODELS

DATA REQUIRED

YEAR 1 2 3 4

INITIAL EARNINGS 6

RETENTION RATE 0,7 0,45 0,3 0,

DISCOUNT RATE 0,

Year Earnings Retention Dividend Retention Rate of NPV

Ratio Return

DIVIDEND MODEL

YEAR Dividends PVF Present Value 1 1,80 0,9091 1, 2 4,22 0,8264 3, 3 6,10 0,7513 4,

EARNINGS MODEL

YEAR Earnings NPVs PVF Present Value 1 6,00 10,0000 60, 1 12,60 0,9091 11, 2 6,91 0,8264 5, 3 2,62 0,7513 1, 79,

YEAR Earnings Dividends 1 6,0000 1,8000 1, 2 7,6800 4,2240 5, 3 8,7168 6,1018 9, 4 9,2398 6,9299 14, 5 9,4708 7,1031 18, 6 9,7076 7,2807 22, 7 9,9503 7,4627 26,

RATE OF RETURN ON

INVESTMENT

Terminal Value

Present Value of Cumulative Dividends

Gro

Dividends

Change the values - outcomes should change

Gro

Growth of Dividends

Years

Dividends

WORKSHEET - VALUATION MODELS

DATA REQUIRED

YEAR 1 2 3 4 5

INITIAL EARNINGS 6

RETENTION RATE 0,70 0,45 0,30 0,25 0,

DISCOUNT RATE 0,

GROWTH RATE 0,28 0,135 0,06 0,

Year Earnings Retention Dividend Retentions Rate of NPV

Ratio Investment Return

DIVIDEND MODEL

YEAR Dividends PVF Present Value

RATE OF RETURN ON

INVESTMENT

Terminal Value

EXAMPLE :

Ramon plc is expected to produce earnings next year of £6m. It is expected to invest 70 per cent of its earnings next year to obtain a rate of return of 40 per cent. Over the next three years it is expected to invest 45, 30, and 25 per cent of eanings respectively.In the longer term the company is expected to invest 25 per cent of earnings per annum. The rate of return in years two and three are anticipated to be 30 and 20 per cent respectively. From year four onwards the company is not expected to produce a rate of return more than 10 per cent - the minimum rate of return acceptable to shareholders. Change the values to experiment.

The valuation model developed below differentiates between an intial period of non-sustainable growth and the longer te when it is assumed that the growth rate is slower but sustainable. It is assumed that the period of rapid growth lasts for f years or less, but the model is easily ammended to incorporate longer periods of non-sustainable growth. Enter the valu for the expected earnings for next year, the retention ratios for the next five years, the rates of return on the investments financed by the retentions, and the required rate of return i.e. the discount rate. The functioning of the model is illustrate using the example of the valuation of Ramon plc. To use the model replace the values in the initial table for those of Ram plc.

EARNINGS MODEL

  • 8 10,1990 7,6493 30,
  • 9 10,4540 7,8405 33,
  • 10 10,7153 8,0365 36,
  • 11 10,9832 8,2374 39,
  • 12 11,2578 8,4434 42,
  • 13 11,5393 8,6544 44,
  • 14 11,8277 8,8708 47,
  • 15 12,1234 9,0926 49,
  • 16 12,4265 9,3199 51,
  • 17 12,7372 9,5529 53,
  • 18 13,0556 9,7917 55,
  • 19 13,3820 10,0365 56,
  • 20 13,7165 10,2874 58,
  • 21 14,0595 10,5446 59,
  • 22 14,4109 10,8082 60,
  • 23 14,7712 11,0784 62,
  • 24 15,1405 11,3554 63,
  • 25 15,5190 11,6393 64,
  • 26 15,9070 11,9302 65,
  • 27 16,3047 12,2285 66,
  • 28 16,7123 12,5342 67,
  • 29 17,1301 12,8476 68,
  • 30 17,5583 13,1688 68,
  • 31 17,9973 13,4980 69,
  • 32 18,4472 13,8354 70,
  • 33 18,9084 14,1813 70,
  • 34 19,3811 14,5358 71,
  • 35 19,8656 14,8992 71,
  • 36 20,3623 15,2717 72,
  • 37 20,8713 15,6535 72,
  • 38 21,3931 16,0448 73,
  • 39 21,9280 16,4460 73,
  • 40 22,4762 16,8571 74,
  • 41 23,0381 17,2785 74,
  • 42 23,6140 17,7105 74,
  • 43 24,2044 18,1533 75,
  • 44 24,8095 18,6071 75,
  • 45 25,4297 19,0723 75,
  • 46 26,0655 19,5491 75,
  • 47 26,7171 20,0378 76,
  • 48 27,3850 20,5388 76,
  • 49 28,0696 21,0522 76,
  • 50 28,7714 21,5785 76,
  • 51 29,4907 22,1180 76,
  • 52 30,2279 22,6710 76,
  • 53 30,9836 23,2377 77,
  • 54 31,7582 23,8187 77,
  • 55 32,5522 24,4141 77,
  • 56 33,3660 25,0245 77,
  • 57 34,2001 25,6501 77,
  • 58 35,0551 26,2914 77,
  • 59 35,9315 26,9486 77,
  • 60 36,8298 27,6224 77, - 0, - 5, - 10, - 15, - 20, - 25, - 30,
  • 1 1,80 0,9091 1,
  • 2 4,22 0,8264 3,
  • 3 6,10 0,7513 4,
  • 4 6,93 0,6830 4,
  • 5 7,10 0,6209 4,
  • 5 97,08 0,6209 60,
    • 79,
  • 1 6,00 10,0000 60, YEAR Earnings NPVs PVF Present Value
  • 1 12,60 0,9091 11,
  • 2 6,91 0,8264 5,
  • 3 2,62 0,7513 1,
  • 4 0,00 0,6830 0,
  • 5 0,00 0,6209 0,
    • 79,