Financial Modeling (AcFn 3051)

Chapter One: Introduction to Financial Modeling and Valuation

1.1. Introduction to financial modeling
A financial model is a mathematical representation of the financial operations and financial
statements of a company. It is used to forecast future financial performance of the company by
making relevant assumptions of how the company would fair in the coming financial years. The
financial model provides you with a powerful tool for articulating your business idea and
assisting funding sources in determining a value profile for your company. Your financial model
is a key management tool. If built correctly, it will provide invaluable assistance in
understanding, managing, presenting and explaining your business idea or operations. It can
assist you in the simple budgeting of cash, or it can serve as the primary basis for a valuation of
your company.

Definition:
Financial Modeling is the process by which a firm constructs a financial representation of some,
or all, aspects of the firm or given security. The model is usually characterized by performing
calculations, and makes recommendations based on that information. The model may also
summarize particular events for the end user and provide direction regarding possible actions
or alternatives.

Financial modeling is the construction of spreadsheet models that illustrate a company's likely
financial results in quantitative terms. Financial models can simulate the effect of specific
variables so that the company can plan a course of action should they occur.

Financial models are not about absolute values; they are about relationships. A good financial
model demonstrates the relationships and the business tradeoffs that compose the profitability
potential of the business idea. If you understand the relationships, the drivers of revenue, drivers
of cost, and critical success factors, you understand the core of the business.

A financial model is a tool (typically built in Excel) that displays possible solutions to a real-
world financial problem. And financial modeling is the task of creating a financial model. Every
spreadsheet is not a financial model. What makes a financial model distinct from a garden-
variety spreadsheet? In contrast to a basic spreadsheet, a financial model

Is more structured. A financial model contains a set of variable assumptions — inputs,

outputs, calculations, and scenarios. It often includes a set of standard financial forecasts —

1|Page
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
 such as a profit-and loss statement, a balance sheet, and a cash flow statement — which are
based on those assumptions.
Is dynamic. A financial model contains inputs that, when changed, impact the calculations
and, therefore, the results. A financial model always has built-in flexibility to display different
outcomes or final calculations based on changing a few key inputs.
Uses relationships between several variables. When the user changes any of the input
assumptions, a chain reaction often occurs. For example, changing the growth rate will
change the sales volume; when the sales volume changes, the revenue, sales commissions,
and other variable expenses will change.
Shows forecasts. Financial models are almost always looking into the future. Financial
modelers often want to know what their financial projections will look like down the road.
For example, if you continue growing at the same rate, what will your cash flow be in five
years?
Contains scenarios (hypothetical outcomes). Because a model is looking forward instead
of backward, a well-built financial model can be easily used to perform scenario and
sensitivity analysis. What would happen if interest rates went up? How much can we
discount before we start making a loss?

More broadly, a financial model is a structure (usually in Excel) that contains inputs and outputs,
and is flexible and dynamic.

Who uses it?

Many types of people build and use financial models for different purposes and goals. Bankers,
particularly investment bankers, are heavy users of financial models. Due to the very nature of
financial institutions, modeling is part of the culture of the company — the business’s core is
built on financial models. Banks and financial institutions must comply with current regulatory
restrictions, and the tools and controls in place are forever changing and adapting. Because of
the risk associated with lending and other financial activities, these institutions have very
complex financial modeling systems in place to ensure that the risk is managed effectively.

Types of Financial Model:

There are various kinds of financial models that are used according to the purpose and need of
doing it. Different financial models solve different problems. While majority of the financial
models concentrate on valuation, some are created to calculate and predict risk, performance of

2|Page
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
portfolio, or economic trends within an industry or a region. The following are the different
types of financial models:

Types of financial Description

model

Integrated financial In this model, the three financial statements are linked together
statement models properly so that when one changes, they also change.
(three-way financial
model):

Discounted cash flow A valuation method uses projected free cash flow and discounts
model: them to arrive at a present value which helps in evaluating the
potential of an investment.

Comparative company A process used to evaluate the value of a company using the metrics
analysis model: of other businesses of similar size in the same industry.

Sum-of-the-parts A process of determining what the individual divisions of a company

model/ break-up would be worth if they were spun off or bought by a different
analysis. company.

Leveraged buyout (LBO) Uses to evaluate a leveraged buyout (LBO) transaction, which is the
model: acquisition of a company that is funded using a significant amount
of debt.

Merger & acquisition Uses to evaluate the purchase of a target company, typically a
(M&A) model: strategic buyer. It focuses on "Accretion / Dilution Analysis".
Accretive (increasing) or dilutive (decreasing) of the acquirer's future
earnings per share (after the transaction).

Option pricing model: On, to buy or sell the underlying instrument at a specified price on or
before a specified future date.

1.2. Overview of excel functions for Financial modeling

Excel is a spreadsheet program that is used to record and analyze numerical data. Think of a
spreadsheet as a collection of columns and rows that form a table. Alphabetical letters are
usually assigned to columns and numbers are usually assigned to rows. The point where a
3|Page
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
column and a row meet is called a cell. The address of a cell is given by the letter representing
the column and the number representing a row (example, A1, B1, C1 and so on). The ribbon is
used to access various commands in Excel. The options dialogue window allows you to
customize a number of items i.e. the ribbon, formulas, proofing, save, etc.

Ribbon components are:

Ribbon start button – it is used to access commands i.e. creating new documents, saving
existing work, printing, accessing the options for customizing Excel, etc.

Ribbon tabs – the tabs are used to group similar commands together. The home tab is used for
basic commands such as formatting the data to make it more presentable, sorting and finding
specific data within the spreadsheet.

Ribbon bar – the bars are used to group similar commands together. As an example, the
Alignment ribbon bar is used to group all the commands that are used to align data together.

Understanding the worksheet (Rows and Columns, Sheets, Workbooks)

A worksheet is a collection of rows and columns. When a row and a column meet, they form a
cell. Cells are used to record data. Each cell is uniquely identified using a cell address. Columns
are usually labelled with letters while rows are usually numbers.

A workbook is a collection of worksheets. By default, a workbook has three cells in Excel. You
can delete or add more sheets to suit your requirements. By default, the sheets are named
Sheet1, Sheet2 and so on and so forth. You can rename the sheet names to more meaningful
names i.e. daily Expenses, Monthly Budget, etc.1

1
Dear students, in this course I assume that you know the basics of finance and can solve by
hand most of the problems for which you will be creating models. I also assume that you are
4|Page
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
1.3. Basic Financial Calculations using excel
Modeling financial situations will sometimes require tools (programming languages) that are
more systematic than Excel. For example, Visual Basic for Applications (VBA), R and Python.
However, for those who have not worked with other programs or programming languages for
financial modeling, it is worthwhile to point out that one of the important advantages of Excel is
that with Excel you can create excellent output with very little work.

Before all else ~ Data Tables

Financial Modeling makes extensive use of data tables. Dear students, I recommend you first
make sure that you understand the data tables (read Chapter 28, especially sections 28.1–28.5).2
Data tables are absolutely critical in the sensitivity analysis that is part of most financial models.

Some basic financial function formulas are3:

 The syntax of the PV function is: PV(rate,nper,pmt,fv,type) FV and type are optional
arguments and if omitted, they are assumed to be zeros.4
 The syntax of the FV function is: FV(rate,nper,pmt,pv,type). Pv and type are optional
arguments and if omitted, they are assumed to be zeros.
 The syntax of the PMT function is: PMT(rate,nper,pv,fv,type). FV and type are optional
arguments, and if omitted they are assumed to be zeros.
 The syntax of the NPER function is: NPER(rate,pmt,pv,fv,type). Fv and type are optional
arguments and if omitted, they are assumed to be zeros.

familiar with the basics of Excel and have experience creating spreadsheet solutions to at least
simple problems.
2
Simon_Benninga_Tal_Mofkadi__-_Financial_Modeling__Fifth_Edition-MIT_Press_(2022).pdf
3
See Lab exercise for financial modeling [Link]
4
FV (Future Value): This parameter represents the future value or a cash balance you want to attain after
making the final payment. It is an optional parameter in the PV function. If you omit the FV parameter,
Excel assumes it to be 0 (zero). The future value can be a positive number representing a cash balance you
want to achieve or a negative number representing a cash balance you want to pay off.
Type: This parameter indicates the timing of the payment in relation to the period. It is also an optional
parameter in the PV function. The "type" parameter can have two values: 0 or 1.
- If the payment is made at the end of the period, you should use 0.
- If the payment is made at the beginning of the period, you should use 1.

5|Page
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
 The syntax of the RATE function is: RATE(nper,pmt,pv,fv,type,guess). Fv, type, and guess
are optional arguments. If FV and type are omitted, they are assumed to be zeros.
 The syntax of the IPMT function is: IPMT(rate,per,nper,pv,fv,type). Per is the period number
for which the interest amount is to be calculated.
 The syntax of the PPMT function is: PPMT(rate,per,nper,pv,fv,type). Per is the period
number for which the principal amount is to be calculated. FV and type are optional
arguments and if omitted, they are assumed to be zeros.
 The syntax of the CUMIPMT function is:
CUMIPMT(rate,nper,pv,start_period,end_period,type). Start_period and end_period
specify the consecutive periods for which the cumulative principal payment is to be
calculated. Note that unlike in the other functions, here type is not optional.
 The syntax of the CUMPRINC function is:
CUMPRINC(rate,nper,pv,start_period,end_period,type). Start_period and end_period
specify the consecutive periods for which the cumulative principal payment is to be
calculated. Note that unlike in some of the other functions, here type is not optional.
 The syntax of the NPV function is: NPV(rate,value1,value2,...). Rate is the discount rate per
period. Value1, value2, and so on, are up to 29 arguments representing the cash flows in
order (earliest first). They must be equally spaced in time. You can specify both cash inflows
and cash outflows by using appropriate signs.
 The syntax of the IRR function is: IRR(values,guess). Values are an array or range containing
the cash flows. Alternately, we can compute the IRR by using Excel’s Goal Seek.

1.4. Present value and Net Present Value

Almost all financial problems are centered on finding the present value of a series of cash
receipts over time. The cash receipts (or cash flows) may be certain or uncertain. The present

value of a cash flow Ft anticipated to be received at time is ( )

. The numerator of this

expression is usually understood to be the expected cash flow at time t, and the discount rate r
in the denominator is adjusted for the riskiness of this expected cash flow—the higher the risk,
the higher the discount rate.

The basic concept in present value calculations is the concept of opportunity cost. Opportunity
cost is the return that would be required of an investment to make it a viable alternative to
other investments with similar risk characteristics. In the financial literature, there are many
synonyms for opportunity cost; among them are discount rate, cost of capital, and interest rate.
6|Page
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
When applied to risky cash flows, we will sometimes call the opportunity cost the risk-adjusted
discount rate (RADR) or the weighted average cost of capital (WACC). It goes without saying
that this discount rate should be risk-adjusted (i.e., the cost of capital should reflect the same
risk as the expected cash flow), and much of the standard finance literature discusses how to do
this. As illustrated below, when we calculate the net present value, we use the investment’s
opportunity cost as a discount rate. When we calculate the internal rate of return, we compare
the calculated return to the investment’s opportunity cost to judge its value.

The concepts of present value and net present value are related to the value today of a set of
future anticipated cash flows. As an example, suppose we are valuing an investment that
promises $100 per year at the end of this and the next four years. We suppose that these cash
flows are risk-free: There is no doubt that this series of five payments of $100 each will actually
be paid. If a bank pays an annual interest rate of 4% on a 5-year deposit, then this 4% is the
investment’s opportunity cost, the alternative benchmark return to which we want to compare
the investment. We can calculate the value of the investment by discounting its cash flows using
this opportunity cost as a discount rate.
Table 1: Computing the PV
A B C D
1 Computing the Present Value
2 Discount Rate 4%
3 Year Cash Flow Present Value
4 0
5 1 100 96.15 ⇐ B5/(1+$B$2)^A5
6 2 100 92.46 ⇐ B6/(1+$B$2)^A6
7 3 100 88.90 ⇐ B7/(1+$B$2)^A7
8 4 100 85.48 ⇐ B8/(1+$B$2)^A8
9 5 100 82.19 ⇐ B9/(1+$B$2)^A9
10 Present Value
11 Summing cells C5:C9 $445.18 ⇐ SUM(C5:C9)
12 Using Excel's NPV Function $445.18 ⇐ NPV(B2,B5:B9)
13 Using Excel's PV Function $445.18 ⇐ PV(B2,5,-100)

The present value, 445.18, is the value today of the investment. In a competitive market, the
present value should correspond to the market price of the cash flows. The spreadsheet above
illustrates three ways of obtaining this value.

7|Page
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
The Difference between Excel’s PV and NPV Functions

The spreadsheet above may leave the misimpression that PV and NPV perform exactly the same
computation. But this is not true—whereas NPV can handle any series of cash flows, PV can
handle only constant cash flows.

Table 2: Computing the PV: unequal series of cash flow

A B C D
Computing the Present Value
In this example the cash flows are not equal
Either discount each cash flow separately or use Excel's NPV function
Excel's PV doesn't work for this case
14
15 Discount Rate 4%
16 Year Cash Flow Present Value
17 0
18 1 100 96.15 ⇐ B18/(1+$B$15)^A18
19 2 200 184.91 ⇐ B19/(1+$B$15)^A19
20 3 300 266.70 ⇐ B20/(1+$B$15)^A20
21 4 400 341.92 ⇐ B21/(1+$B$15)^A21
22 5 500 410.96 ⇐ B22/(1+$B$15)^A22
23 Present Value
24 Summing cells C18:C22 $1,300.65 ⇐ SUM(C18:C22)
25 Using Excel's NPV Function $1,300.65 ⇐ NPV(B15,B18:B22)

Excel’s NPV Function Is Misnamed!

In standard finance terminology, the present value of a series of cash flows is the value today of
the future cash flows:
N
Ft
P esent Value ∑
( )t
t

The net present value is the present value minus the cost of acquiring the asset (the cash flow at
time zero):
N
Ft
Net P esent Value ∑ ∑(
( )t )
t

In many cases, CF0 < 0,

meaning that it represents This is the present value,
the price paid for the asset. given by Excel’s NPV
Function

8|Page
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
Suppose that the above investment is sold for $1,500. Clearly it would not be worth its purchase
price since—given the alternative return (discount rate) of 4%—the investment is worth only
$1,300.65. The net present value (NPV) is the applicable concept here. Denoting by r the
discount rate applicable to the investment, the NPV is calculated as

∑( )

Where CFt is the investment’s cash flow at time t and CF0 is today’s cash flow. Suppose, for
example, that the series of five cash flows of $100 is sold for $250. Then, as shown below, the
NPV = 195.18.

Table 3: Computing the PV and NPV

A B C D
1 Computing the Present Value
2 Discount Rate 4%
3 Year Cash Flow Present Value
4 0 -250 -250.00 ⇐ B4/(1+$B$2)^A5
5 1 100 96.15 ⇐ B5/(1+$B$2)^A5
6 2 100 92.46 ⇐ B6/(1+$B$2)^A6
7 3 100 88.90 ⇐ B7/(1+$B$2)^A7
8 4 100 85.48 ⇐ B8/(1+$B$2)^A8
9 5 100 82.19 ⇐ B9/(1+$B$2)^A9
10 Present Value
11 Summing cells C4:C9 $195.18 ⇐ SUM(C4:C9)
12 Using Excel's NPV Function $195.18 ⇐ B4 + NPV(B2,B5:B9)

The NPV represents the wealth increment that accrues to the purchaser of the cash flows. If you
buy the series of five cash flows of 100 for 250, then you have gained 195.18 in wealth today. In
a competitive market, the NPV of a series of cash flows ought to be zero. Since the present
value should correspond to the market price of the cash flows, the NPV should be zero. In other
words, in a competitive market, assuming that 4% is the correct risk-adjusted discount rate, the
market price of our five cash flows of 100 ought to be 445.18.

In an efficient capital market, purchasing or selling any security at the prevailing market price is
a zero–NPV transaction. That is, you can expect to earn only a return adequate to compensate
you for the risk you bear. Conceptually, we will conclude that managers can and do find positive
NPV investment projects. Uncovering these investment opportunities in real assets is the
financial manager’s main contribution to creating value (wealth) for their shareholders.

9|Page
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
Here are some specific ways that positive NPV projects can exist or be created:

 Hire innovative employees and provide then with incentives to be creative in developing
ideas for revenue enhancement, cost reduction, product innovation, research and
development, etc.
 Introduce a new product.
 Develop a core technology.
 Create barriers to entry.
 Create product differentiation by aggressive advertising and marketing.
 Utilize organizational innovation.

However, positive NPV opportunities exist in real asset markets relates to the relative efficiency
of the markets in which these real assets trade. This is due to the fact that the real asset markets
are less efficient than financial asset markets. Barriers to entry, high transaction costs, patent
protection, regulatory restrictions, imperfect information, oligopolistic industries, asset
illiquidity, etc., are common to real asset markets.

The Present Value of an Annuity and a Perpetuity—Some Useful Formulas

An annuity is a security that pays a constant cash flow in each period in the future. Annuities
may have a finite series of payments. When the series is infinite, it is called perpetuity. When the
periodic cash flow is ―C‖ and the appropriate discount rate is r, then the value today of the
annuity, paying n periods its present value, is:

C C C C C 1
Present Value of annuity + + + + (1- )
(1+r)1 (1+r)2 (1+r)3 (1+r)n r (1+r)n

If cash stream promises an infinite series of constant future payments (n → ∞), then this formula
reduces to:

C C C C
Present Value of perpetuity 1
+ 2
+ +
(1+r) (1+r) (1+r)3 r

Both formulas can be computed with Excel. Below we compute the value of a finite annuity in
three ways—using the formula (cell B31), using Excel’s PV function (cell B32), and using Excel’s
NPV function (cell B40). The value of perpetuity (an infinite annuity) can be computed as shown
in (Cell B42).

10 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
Table 4: Computing the value of annuity
A B C
26 Computing the value of annuity
27 Periodic Payment, C 1,000
28 Number of future periods paid, n 5
29 Discount Rate, r 6%
30 Present Value of annuity
31 Using formula 4,212.36 ⇐ B27*(1-1/(1+B29)^B28)/B29
32 Using Excel's PV Function 4,212.36 ⇐ PV(B29,B28,-B27)
33 Period Annuity payment
34 0
35 1 1,000.00 ⇐ $B$27
36 2 1,000.00
37 3 1,000.00
38 4 1,000.00
39 5 1,000.00
40 PV using excel's NPV function 4,212.36 ⇐ NPV(B29,B35:B39)
41 The value of perpetuity (an infinite annuity)
42 Present values of annuity 16,666.67 ⇐ B27/B29

The Value of a Growing Annuity

A growing annuity pays out a sum C, which grows at a periodic growth rate g. If the annuity is
finite, its value today is given by:

C C(1+g) C(1+g)2 C(1+g)n-1

PV of growing annuity + + + +
(1+r)1 (1+r)2 (1+r)3 (1+r)n

C 1+g n
( ( ) )
r-g 1+r

Taking this formula and letting n → ∞, we can compute the value of a growing perpetuity
(infinite growing annuity) as follows:

C C(1+g) C(1+g)2
PV of growing annuity + + +
(1+r)1 (1+r)2 (1+r)3

11 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
Table 5: Computing the value of a finite growing annuity
A B C
43 Computing the value of a finite growing annuity
44 Periodic Payment, C 1,000
45 Growth rate of payments, g 3%
46 Number of future periods paid, n 5
47 Discount Rate, r 6%
48 Present Value of annuity
49 Using formula 4,457.43 ⇐ B44*(1-((1+B45)/(1+B47))^B46)/(B47-B45)
50 Period Annuity payment
51 0
52 1 1,000.00 ⇐ $B$44
53 2 1,030.00 ⇐ $B$44*(1+$B$45)^(A53-$A$52)
54 3 1,060.90 ⇐ $B$44*(1+$B$45)^(A54-$A$52)
55 4 1,092.73 ⇐ $B$44*(1+$B$45)^(A55-$A$52)
56 5 1,125.51 ⇐ $B$44*(1+$B$45)^(A56-$A$52)
57 PV using excel's NPV function 4,457.43 ⇐ NPV(B47,B52:B56)

When the growing annuity has an infinite life, it is called growing perpetuity. The next
spreadsheet shows how its value is computed.
Table 6: Computing the value of growing perpetuity annuity
A B C
58 Computing the value of growing perpetuity (an infinite growing annuity)
59 Periodic Payment, C 1,000 ⇐ Starting at date 1
60 Growth rate of payments, g 3%
61 Number of future periods paid, n 5
62 Discount Rate, r 6%
63 Present Value of annuity 33,333.33 ⇐ =IF(B60<B62,B59/(B62-B60),"NA")

Decision Criterion for the NPV method

If projects are independent, accept if the project NPV > 0.
If projects are mutually exclusive, accept projects with the highest positive NPV, those that add the most value.

Exercises: Discounting and Capitalizing

1. Your daughter is currently 8 years old. You anticipate that she will be going to college in ten
years. You would like to have 100,000 € in a savings account to fund her education at that

12 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
 time. If the account promises a fixed interest rate of 3% per year, how much money do you
need to put into the account today to ensure that you will have 100,000 € in ten years?
A B C
1 FV 100,000.00 €
2 RATE 3%
3 NPER 10 Excel Formula
4 PV 74,409.39 € = B3/(1+B4)^B5
2. You are thinking of retiring. Your retirement plan will pay you either 250,000 € immediately
on retirement or 350,000 € five years after the date of your retirement. Which alternative
should you choose if the interest rate is 0% per year? 8% per year? 20% per year?

RATE PV Possible formulas

Cash flow at year 0 250,000.00 € 0% 350,000.00 € =350,000/(1+0%)^5
Cash flow at year 5 350,000.00 € 8% 238,204.12 € =350,000/(1+8%)^5
NPER 5 20% 140,657.15 € =350,000/(1+20%)^5
3. Your grandfather put some money in an account for you on the day you were born. You are
now 18 years old and are allowed to withdraw the money for the first time. The account
currently has 3,996 € in it and pays an 8% interest rate. How much money would be in the
account if you let the money there until your 25th birthday? What if you left the money until
your 65th birthday? How much money did your grandfather originally put in the account?

FV age Possible formulas

outstanding amount 3,996.00 € 18 Given
6,848.44 € 25 =3,996*(1+0.08)^(25-18)
RATE = 8% 148,779.12 € 65 =3,996*(1+0.08)^(65-18)
1,000.00 € 0 =3,996/(1+0.08)^18
4. You have a loan outstanding. It requires making three annual payments at the end of the
next three years of 1,000 € each. Your bank has offered to allow you to skip making the next
two payments in lieu of making one large payment at the end of the loan’s term in three
years. If the interest rate on the loan is 5%, what final payment will the bank require you to
make so that it is indifferent between the two forms of payment?

Years 1 2 3
Annual payments 1,000.00 € 1,000.00 € 1,000.00 €
RATE 5%
FV 1,102.50 € 1,050.00 € 1,000.00 € 3,152.50 €
Possible formulas =1,000*(1+0.05)^2 =1,000*(1+0.05)^1 =1,000.00 =SUM(1:3)

13 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
1.5. The Internal Rate of Return (IRR) and Loan Tables
The internal rate of return (IRR) is defined as the compound rate of return r, which makes the
NPV equal to zero:

( | ( ) ) ∑
( )

To illustrate, consider the example given in rows 3–8 in the spreadsheet below: a project costing
800 in year zero returns a variable series of cash flows at the end of years 1 to 5. The IRR of the
project (cell F9) is 19.54%. Note that the Excel IRR function includes as arguments all of the cash
flows of the investment, including the first (in this case negative) cash flow of −800.

Table 7: Computing the IRR

E F G
1 Computing the Internal Rate of Return
2 Year Cash flow
3 0 -800
4 1 100
5 2 200
6 3 300
7 4 400
8 5 500
9 Internal Rate of Return (IRR) 19.54% ⇐ =IRR(F3:F8)

There is no simple mathematical formula to compute IRR. Excel’s IRR function uses trial and
error, which can be simulated by using trial and error in a spreadsheet, as illustrated below. We
start with a discount rate (6% in the example below) and check the project’s NPV:

Table 8: Computing the NPV and IRR

E F G
1 Computing the Internal Rate of Return
2 Discount Rate, r 6%
3 Year Cash flow
4 0 -800
5 1 100
6 2 200
7 3 300
8 4 400
9 5 500
10 Net present value (NPV) 414.69 ⇐ =NPV(F2, F5:F9)+F4

14 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
By playing with the discount rate or by using Excel’s Goal Seek (under Data|What-if analysis), we
can determine that at 19.54% the NPV in cell G10 is zero. We allow Excel to change the interest
rate to set the NPV to zero by pointing to the NPV as the Set cell by changing the interest rate
cell. Here’s the way the Goal Seek screen looked after we got the correct answer:

Table 9: Computing the IRR: Goal Seek

Alternative formula to compute the IRR is the interpolation technique.

 Finding the discount rate that result in a zero NPV. Where:
npv1  r1 discount rate that results in a positive NPV
IRR  r1  * r2  r1   r2 discount rate that results in a negative NPV
npv1  npv2
 npv1 positive NPV
 npv2 negative NPV.

Decision Criterion for the IRR method

If IRR > WACC, the project’s rate of return is greater than its costs. Thus, there is some return left over to boost
stockholders’ returns.
If projects are independent, accept both projects, if both have IRR > k
If projects are mutually exclusive, accept the project with better IRR
If IRR < k, reject the project.

Analysis the Crossover rate:

15 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
Crossover rate is referred to as the rate of return at which the net present values of two projects
are equal. It is representative of the rate of return at which the net present value of one profile
intersects with the net present value of another project.

Example: Projects A and B have the following cash flows. The required return is 10%. NPV and
IRRs are shown below. What is the crossover rate? Plot the crossover rate on the NPV profile.

Table 10: Computing the Crossover rate

Year Project A Project B Project A - Project B
0 -500 -400 -100
1 325 325 0
2 325 200 125
IRR 19.43% 22.17% 11.80% <-- Crossover rate
NPV $ 64.05 $ 60.74

16 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
 Plot the crossover rate on the NPV profile
A graph showing the
Discount Rate NPV (A) NPV (B) relationship between a project’s
1% $ 140.38 $ 117.84 NPV and the firm’s cost of
2% $ 131.01 $ 110.86 capital (IRR). NPV profiles of
projects can cross when
3% $ 121.88 $ 104.05
4% $ 112.98 $ 97.41  project size differences exist
5% $ 104.31 $ 90.93 (the cost of one project is
larger than that of the other)
6% $ 95.85 $ 84.60 or
7% $ 87.61 $ 78.43  timing differences exist
8% $ 79.56 $ 72.39 (most of the cash flows from
one project come in the
9% $ 71.71 $ 66.50
early years, while most of
10% $ 64.05 $ 60.74 the cash flows from the
11% $ 56.57 $ 55.12 other project come in the
12% $ 49.27 $ 49.62 later years)
13% $ 42.13 $ 44.24 The point where net present
14% $ 35.16 $ 38.98 value profile crosses the
15% $ 28.36 $ 33.84 horizontal axis indicates a
project’s internal rate of
16% $ 21.70 $ 28.80 return.
17% $ 15.19 $ 23.88
18% $ 8.83 $ 19.06  If the cost of capital is
greater than this crossover
19% $ 2.61 $ 14.34 rate, the two methods give
20% $ -3.47 $ 9.72 same answer.
21% $ -9.43 $ 5.20  If the cost of capital less
than crossover rate, two
22% $ -15.25 $ 0.77
methods give separate
23% $ -20.95 $ -3.58 answers
24% $ -26.53 $ -7.83
25% $ -32.00 $ -12.00
26% $ -37.35 $ -16.09

In the diagram below, it can be seen that both the NPVs have been plotted on a graph.
Individually, Project A has a rate of return of 19.43% whereas for Project B, the Rate of Return is
around 22.17%. However, they both have a crossover rate at 11.80%, which is depicted by the
intersection of both the lines in the diagram above.

From a purely technical point of view, a set of cash flows can have multiple IRRs only if it has at
least two changes of sign. Many typical cash flows have only one change of sign. Consider, for
example, the current market price of project A is $500, then the stream of cash flows changes
signs only once (from negative in year zero to positive in years 1 to 2). Thus, there is only one
IRR.

17 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
 The NPV profile
$160.00
$140.00
$120.00 NPV (A)
$100.00
$80.00 Crossover rate, 11.8%
$60.00
NPV

$40.00 IRR (A), 19.43%

$20.00 NPV (B) IRR (B),22.17%
$0.00
1%
2%
3%
4%
5%
6%
7%
8%
9%
10%
11%
12%
13%
14%
15%
16%
17%
18%
19%
20%
21%
22%
23%
24%
25%
26%
-$20.00
-$40.00
-$60.00
Cost of Capital

NPV (A) NPV (B)

Loan Table: Flat Payment Schedules

Another common problem is to compute a ―flat‖ repayment for a loan. It is the rate always
computes the interest on the initial amount of loan for each period. It is highly applied in
microfinance institutions. Do you reimburse the interest what you pay each period on the same
initial amount? The flat rate doesn’t make a sense in finance, or no financial meaning (see table
11 below). For example: You take a loan for $10,000 at monthly rate of 0.5% to finance a car. The
bank wants you to make a series of payments that will pay off the loan and the interest over 12
months. Determine the monthly installment payment? What is the monthly compounding
interest rate? What is the annual effective interest rate? Show the amount of monthly interest
payment in loan table.
The case to finance a car
 Amount to be financed: $ 10,000
 Maturity: 12 months
 Monthly rate: 0.5%
 Monthly installment: (10,000/12) + (0.5% * 10,000) = $ 883.33
Tips:
Annual rate and Monthly Rate
Formulas,
• (1+annual rate) ^1 = (1+monthly rate)^12
• Where, 1 is year and 12 is the number of month in a year.
• Thus,
• PMT(1+annual rate) = PMT(1+monthly rate)^12 multiplied both sides by 1/PMT
18 | P a g e
• 1+annual rate = (1+monthly rate)^12
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
• Annual rate = (1+monthly rate)^12 – 1
• Monthly rate = (1+annual rate)^(1/12) – 1
Table 11: Loan table using the monthly flat rate
A B C D E F G
1 Loan table with the Flat Interest rate: 0.5%
2 Loan $ 10,000.00
3 Maturity 12
4 Monthly Rate 0.5%
5 Monthly Instalment 883.33 <--=(B2/B3)+(B2*B4)

Interest Reimbursement
6 Month Instalment Interest Outstanding amount (amount repaid)
7 $ 10,000.00
8 1 883.33 50.00 9166.67 0.50% 833.33 Possibile formulas
9 2 883.33 50.00 8333.33 0.55% 833.33 (1) Interest =
monthly flat rate *
10 3 883.33 50.00 7500.00 0.60% 833.33
initial loan amount
11 4 883.33 50.00 6666.67 0.67% 833.33 (2) Outstanding =
12 5 883.33 50.00 5833.33 0.75% 833.33 Beginning loan
13 6 883.33 50.00 5000.00 0.86% 833.33 balance - loan
amount reimburse
14 7 883.33 50.00 4166.67 1.00% 833.33 (3) Interest amount
15 8 883.33 50.00 3333.33 1.20% 833.33 = current period
16 9 883.33 50.00 2500.00 1.50% 833.33 interest/ beginning
loan outstanding
17 10 883.33 50.00 1666.67 2.00% 833.33
(4) Loan reimburse
18 11 883.33 50.00 833.33 3.00% 833.33 = Instalment -
19 12 883.33 50.00 0.00 6.00% 833.33 interest

Therefore, how the monthly flat rate of 0.5% converted to monthly compounding rate or
effective monthly interest rate? What is the annual rate? Which means the financial rate or
effective rate?

Monthly effective rate 0.9080% <---=RATE(12,-883.33,10000,0,0) Excel function

1
(1- (1+r)12 ) 10,000, r= 0.908%
Therefore, the annual effective interest rate (1+0.908%)12 -1, 11.46%

19 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
 A B C D E F G
21 Loan table with the Monthly effective Interest rate: 0.908%

Interest Reimbursement
22 Month Instalment Interest Outstanding amount (amount repaid)
23 $ 10,000.00
24 1 883.33 90.80 9207.46 0.91% 792.54
25 2 883.33 83.60 8407.73 0.91% 799.73
26 3 883.33 76.34 7600.74 0.91% 806.99 Possibile formulas
(1) Interest = monthly
27 4 883.33 69.01 6786.42 0.91% 814.32 effective interest rate
28 5 883.33 61.62 5964.70 0.91% 821.71 * initial loan amount
(2) Outstanding =
29 6 883.33 54.16 5135.53 0.91% 829.18 Beginning loan
30 7 883.33 46.63 4298.82 0.91% 836.70 balance - loan amount
reimburse (3) Interest
31 8 883.33 39.03 3454.52 0.91% 844.30
amount = current
32 9 883.33 31.37 2602.55 0.91% 851.97 period interest/
33 10 883.33 23.63 1742.85 0.91% 859.70 beginning loan
outstanding
34 11 883.33 15.82 875.34 0.91% 867.51 (4) Loan reimburse =
35 12 883.33 7.95 0.0 0.91% 875.39 Instalment - interest

How Should I finance my mortgage? A comprehensive case

Mr Mulugeta, CEO of Ras Dejen Ltd., wants to build new headquarters for his fast-expanding
company in Bahir Dar. Working together with a team of renowned national architects, Mr
Mulugeta has found the ideal location for his new headquarters and has also finalized the
construction plans. The new headquarters of Ras Dejen are expected to cost ETB 2,500,000.

To finance Ras Dejen’s new headquarters, Mr Mulugeta has received mortgage offers from 3
different banks. The first offer is from AM Bank, a national bank based in Ethiopia with strong
retail and mortgage positions on the Ethiopian market. The second offer originates from XY
Mortgage Bank, an Ethiopian bank headquartered in Addis Ababa, specializing solely in
mortgage banking. The last offer comes from ABC Bank, a bank providing financial services
specially tailored for young entrepreneurs.

Mr Mulugeta knows that he needs to be careful when choosing the right mortgage contract, not
least because Ras Dejen will bear the cost of the mortgage for the next decades, so a bad choice
now could ruin the growth prospects of the company. To take a good decision, he thought back
to the financial training he had received, sure that the answer lies somewhere in what he had
learnt during the course. Which mortgage offer should Mr Mulugeta opt for? All the proposed
mortgage contracts are shown in table 12 (below). Use them to make your decision.

20 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
Table 12: Mortgage contract alternatives
AM Bank has agreed to loan an amount of ETB 2,500,000 to Ras Dejen Ltd. for the financing of
the headquarter building. The following terms and conditions apply to the loan:
Capital to be financed: ETB 2,500,000.00
Commission: ETB 3,000.00
Term of Loan: 15 years
Monthly Interest Rate: 0.7302%
XY Mortgage Bank has agreed to loan an amount of ETB 2,500,000 to Ras Dejen Ltd. for the
financing of the headquarter building. The following terms and conditions apply to the loan:
Capital to be financed: ETB 2,500,000.00
Commission: ETB 1,500
Term of Loan: 15 years
Monthly Interest Rate: 0.7144%
ABC Bank has agreed to loan an amount of ETB 2,500,000 to Ras Dejen Ltd. for the financing of
the headquarter building. The following terms and conditions apply to the loan:
Capital to be financed: ETB 2,500,000.00
Commission: ETB 2,500.00
Term of Loan: 16 years
Annual Interest Rate: 8.03%
A B C D
1 Financing a building (Ethiopia)

2 AM Bank XY Mortgage Bank ABC Bank

3 Amount ETB 2,500,000.00 ETB 2,500,000.00 ETB 2,500,000.00

4 PV ETB 2,500,000.00 ETB 2,500,000.00 ETB 2,500,000.00

5 Commission ETB 3,000.00 ETB 1,500.00 ETB 2,500.00

6 Number of payments 180 180 16

7 Payments -ETB 25,004.83 -ETB 24,724.70 -ETB 282,984.12

8 FV 0 0 0

9
10 Periodic rates (gross, given) 0.7302% 0.7144% 8.0300%

11 CHECK [=RATE(B6,B7,B3,0,0)] 0.7302% 0.7144% 8.0300%

12 Periodic rates (net, =RATE(B6,B7,B3-B5,B8,0)) 0.7319% 0.7152% 8.0457%

13 Monthly interest rate, net (ABC bank) = (1+D12)^1/12 -1--> 0.6469%

14 AER (gross, =(1+B10)^12-1) 9.12% 8.92% 8.03%

15 AER (net, =(1+B12)^12-1) 9.15% 8.93% 8.05%

16 Total payment with commissions (=B7*B6) -ETB 4,500,869.66 -ETB 4,450,446.66 -ETB 4,527,745.91

17 Total payment without commissions (=B16+B5) -ETB 4,497,869.66 -ETB 4,448,946.66 -ETB 4,525,245.91

Compare the monthly interest rates offered by banks and then select the marginal rate, which is
the best rate from the market. This marginal rate therefore is 0.6469% offered by ABC bank.

21 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
How much could Mr Mulugeta borrow at the marginal rate from AM bank and XY Mortgage
Bank?
A B C D
20 How much could he borrow at the marginal AM Bank XY Mortgage Bank ABC Bank
21 rate? =PV(0.6469%,B6,B7,B8,0) ETB 2,654,320.22 ETB 2,624,584.01 ETB 2,500,000.00

1.6. Future values and Applications

Suppose you deposit $1,000 in an account today, leaving it there for 10 years. Suppose the
account accumulates an annual interest of 10%. How much will you have at the end of 10 years?
The answer, as shown in the spreadsheet below, is $2,593.74.
Table 13: FV
I J K L M
1 Simple Future Value
2 Interest 10%
Account Balance, Interest earned Total in account,
3 Year beginning of year during year end of year Possible formulas
4 1 1,000.00 100.00 1,100.00 >---=J4+K4
5 2 1,100.00 110.00 1,210.00
6 3 1,210.00 121.00 1,331.00
7 4 1,331.00 133.10 1,464.10 =$J$2
8 5 1,464.10 146.41 1,610.51
9 6 1,610.51 161.05 1,771.56
10 7 1,771.56 177.16 1,948.72 = L4
11 8 1,948.72 194.87 2,143.59
12 9 2,143.59 214.36 2,357.95
13 10 2,357.95 235.79 2,593.74
14 A simpler way 2,593.74 <--- =J4*(1+J2)^10

As cell K14 shows, you don’t need all these complicated calculations. The future value of 1,000
in 10 years at 10% per year is given by FV 1,000 * (1+10%)10 2,593.74. Now consider the
following, slightly more complicated problem: Again, you intend to open a savings account.
Your initial deposit of $1,000 today (the beginning of year 1) will be followed by a similar
deposit at the beginning of years 2, 10 (total of 10 deposits at the beginning of each year). If
the account earns 10% per year, how much will you have in the account at the end of year 10?
This problem is easily modeled in Excel, as follows:

22 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
Table 14: FV with annual deposits
I J K L M N
16 Future Value with Annual deposits
17 Interest 10%
18 Annual deposit 1,000.00 <--- made today & at beginning of each next 9 years
19 Number of deposits 10

Account Balance, Deposit at the Interest earned Total in account,

20 Year beginning of year beginning of year during year end of year Possible formulas
21 1 0 1,000.00 100.00 1,100.00 <-- =J21+K21+L21
22 2 1,100.00 1,000.00 210.00 2,310.00
23 3 2,310.00 1,000.00 331.00 3,641.00
24 4 3,641.00 1,000.00 464.10 5,105.10
25 5 5,105.10 1,000.00 610.51 6,715.61 =$J$17*(J21+K21)
26 6 6,715.61 1,000.00 771.56 8,487.17
27 7 8,487.17 1,000.00 948.72 10,435.89
28 8 10,435.89 1,000.00 1,143.59 12,579.48 =M21
29 9 12,579.48 1,000.00 1,357.95 14,937.42
30 10 14,937.42 1,000.00 1,593.74 17,531.17
31 Future Value 17,531.17 <--- =FV(J17,J19,-J18,,1)
5

Thus, the answer is that we will have $17,531.17 in the account at the end of year 10. This same
answer can be represented as a formula that sums the future values of each deposit (cell M30
above):
Total at beginning of year 10 1,000*(1+10%)10 +1,000*(1+10%)9 + . . . + 1,000*(1+10%)1
10

∑ 1,000*(1+10%)t
t 1

A Pension Problem— Consider a typical exercise: You are currently 55 years old and intend to
retire at age 60. To make your retirement easier, you intend to start a retirement account:
 At the beginning of each of years 1, 2, 3, 4, 5 (that is, starting today and at the beginning of
each of the next four years), you intend to make a deposit into the retirement account. You
think that the account will earn 7% per year.
 After retirement at age 60, you anticipate living eight more years. 6 At the beginning of each
of these years you want to withdraw $30,000 from your retirement account. Your account
balances will continue to earn 7%.

5
―Type‖ ( 1) refers to the deposit is made at the beginning of each period.
6
Of course, you’re going to live much longer! And we wish you good health! The dimensions of
this problem have been chosen to make it fit nicely on a page.
23 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
 How much should you deposit annually in the account? A naive analysis is telling you that in
order to provide $30,000 per year for eight years (total of $240,000), you need to contribute
$240,000/5 = $48,000 in each of the first five years. As the spreadsheet shows, this is wrong.
If you make annual deposits of $48,000 you’ll end up with a lot of money at the end of eight
years.
Table 15: A retirement model
A retirement Problem-Basics
Interest 7%
Annual deposit 48,000.00
Annual retirement withdrawal 30,000.00

Account balance, Deposit at Interest earned Total in account,

Year beginning of year beginning of year during year end year Possible formulas
1 0.00 48,000.00 3,360.00 51,360.00 <---=SUM(B6:D6)
2 51,360.00 48,000.00 6,955.20 106,315.20
3 106,315.20 48,000.00 10,802.06 165,117.26
4 165,117.26 48,000.00 14,918.21 228,035.47
5 228,035.47 48,000.00 19,322.48 295,357.96
6 295,357.96 - 30,000.00 18,575.06 283,933.01
7 283,933.01 - 30,000.00 17,775.31 271,708.32 =$B$2*(B6+C6)
8 271,708.32 - 30,000.00 16,919.58 258,627.91
9 258,627.91 - 30,000.00 16,003.95 244,631.86
10 244,631.86 - 30,000.00 15,024.23 229,656.09
11 229,656.09 - 30,000.00 13,975.93 213,632.02
12 213,632.02 - 30,000.00 12,854.24 196,486.26
13 196,486.26 - 30,000.00 11,654.04 178,140.29

 This problem has 5 deposits and 8 annual withdrawals, all made at the beginning of the
year. The beginning of year 13 is the last year of retirement plan; if the annual deposit is
correctly computed, the balance at the beginning (and end) of year 13 after the withdrawal
should be zero.
 The reason is clear—this analysis ignores the return on the accumulated savings and the
powerful effects of compound interest. (If you set the interest rate in the spreadsheet equal
to 0%, you’ll see that you’re right.) This can be solved by Excel’s Solve found on the Data
menu (See, Table 16 below)

1.7. Introduction to valuation and valuation standards

The most important question before even getting into valuation techniques is this: What is
value? To help answer this question we note there are two major categories of value:

1. Book Value: is the value of an asset or entire business entity as determined by its books,
or the financials.
2. Market Value: is the value as determined by the market.

24 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU
Table 16: A retirement model using Solver
A B C D E F
26 A retirement Problem-Basics
27 Interest 7%
28 Annual deposit 31,150.58
29 Annual retirement withdrawal - Account 30,000.00
balance, Deposit at Interest earned Total in account,
30 Year beginning of year beginning of year during year end year
31 1 0.00 31,150.58 2,180.54 33,331.12
32 2 33,331.12 31,150.58 4,513.72 68,995.42
33 3 68,995.42 31,150.58 7,010.22 107,156.22
34 4 107,156.22 31,150.58 9,681.48 147,988.28
35 5 147,988.28 31,150.58 12,539.72 191,678.58
36 6 191,678.58 - 30,000.00 11,317.50 172,996.08
37 7 172,996.08 - 30,000.00 10,009.73 153,005.80
38 8 153,005.80 - 30,000.00 8,610.41 131,616.21
39 9 131,616.21 - 30,000.00 7,113.13 108,729.35
40 10 108,729.35 - 30,000.00 5,511.05 84,240.40
41 11 84,240.40 - 30,000.00 3,796.83 58,037.23
42 12 58,037.23 - 30,000.00 1,962.61 30,000
43 13 30,000 - 30,000 - 0 - 0

Book Value
The book value can be determined by the balance sheet. The total book value of a company’s
property, for example, can be found under the net PP&E in the assets section of the balance
sheet. The book value of the shareholder’s interest in the company (not including the minority
interest holder) can be found under shareholder’s equity.
Market Value
The market value can be defined by its market capitalization, or shares outstanding times share
price. The equity value of a business is the value of the business attributable to just equity
holders; that is, the value of the business excluding debt lenders, minority interest holders, and
other obligations. Shareholders’ equity, for example, is a value of the company’s assets less the
value of the company’s liabilities. So this shareholders’ equity value (making sure non-
controlling interest is not included in shareholders’ equity) is the value of the business excluding
lenders and other obligations; an equity value. The market value, or market capitalization, is
based on the stock price, which is inherently an equity value as equity investors value a
company’s stock excluding debt lenders and other obligations.

End of Chapter One

25 | P a g e
Lecture notes for the course of Financial Modeling, ©Mulualem G., AcFn, COBE, BDU