Excel for Business Valuation: NPV, IRR, PMT and EOMONTH
Excel for Business Valuation: NPV, IRR, PMT and EOMONTH
Joris Kersten, Place: Uden/ Netherlands, April 3rd 2020
Consultant & Trainer Joris Kersten
I am an independent M&A consultant and Valuator from The Netherlands.
In addition, I provide training in “Financial Modelling”, “Business Valuation” and “Mergers & Acquisitions” all over the world (New York, London, Asia, Middle East).
This at leading (“bulge bracket”) investment banks, corporates and universities.
My training in “Business Valuation & Deal Structuring” this March 2020 in The Netherlands is rescheduled due to the corona virus.
But my NEW training calendar in The Netherlands is as follows:
1. 17, 18, 19, 20 and 22, 23 June 2020: 6 days - Business Valuation & Deal Structuring. Location: Uden/ The Netherlands;
2. 24, 25, 26, 27 and 29, 30 June 2020: 6 days - Business Valuation & Deal Structuring. Location: Uden/ The Netherlands;
3. 28, 29, 30, 31 October 2020 + 2, 3 November 2020: 6 days - Business Valuation & Deal Structuring. Location: Amsterdam Zuidas/ The Netherlands;
4. 16, 17, 18, 19 November 2020: 4 days - Financial Modelling in Excel. Location: Amsterdam Zuidas/ The Netherlands.
All info on these open training sessions can be found on: www.joriskersten.nl
And 130 references on my training sessions can be found on: www.joriskersten.nl
Financial modelling in Excel: An Introduction
Everybody who is working in Mergers & Acquisitions (M&A) and/ or business valuation needs to build financial models in excel.
They do this at for example investment banks, but also at M&A boutiques and corporate finance departments of accounting firms.
The financial models typically built for M&A are:
· A discounted cash flow model;
· A leveraged buyout (LBO) model;
· A M&A model to calculate the “accretion or dilution” of a M&A deal.
In order to build these models fast and efficient you need to understand financial modelling in our favourite program “Microsoft Excel”.
In this sequence of blogs I will talk about the different aspects of financial modelling in excel.
And in this sequence I have written several blogs already, you can find these previous articles just below in case you did not read them yet.
The source that I have used is the following brilliant book:
· Using Excel for Business and Financial Modelling: A practical guide – 3rd edition (2019). Author: Danielle Stein Fairhurst. Wiley.
This is one of the best books around on financial modelling!
When you are a corporate finance professional I really recommend you to read & study the book.
And because of this I have decided to use it as the main handbook in my 4-day training “Financial Modelling in Excel”.
All the participants who register receive an original hardcopy of the book.
Earlier blogs on Financial Modelling
Article 1: Financial Modelling in Excel: Circular references, interest calculations and iterations
https://www.dhirubhai.net/pulse/financial-modelling-excel-circular-references-kersten-msc-bsc-rab/
Article 2: Excel basics for Finance: SUM, MAX, MIN, AVERAGE, IF, cell referencing, named ranges
https://www.dhirubhai.net/pulse/excel-basics-finance-sum-max-min-average-cell-named-joris/
Article 3: Excel for Valuation: COUNTIF, VLOOKUP, INDEX and MATCH
https://www.dhirubhai.net/pulse/excel-valuation-countif-vlookup-index-match-kersten-msc-bsc-rab/
Article 4: Excel for Business Valuation: OFFSET, FORECAST and CHOOSE
https://www.dhirubhai.net/pulse/excel-business-valuation-offset-forecast-choose/
Working with dates
Working with dates in excel is always an issue and you need to be careful and consistent.
A very handy function in excel concerning dates is EOMONTH. The function gives you the last day of the month with reference to any date.
In excel you go to a cell and type is =eo and then with TAB you jump into the function.
Then use shortcut ctrl A to be able to enter the “function arguments”.
Do not forget to use the keyboard here and no mouse since the latter is inefficient. Check out the excel shortcuts in the older blog below when you are not familiar with them.
And for my Dutch colleagues, do not forget to put your excel in English, otherwise they do not work.
Article: Excel Shortcuts & Business Valuation:
https://www.dhirubhai.net/pulse/excel-shortcuts-business-valuation-joris-kersten-msc-bsc-rab
When you are in the box of “function arguments” then just press F2 (and “Fn” as well with certain laptops) to jump out of the box and to be able to select cells.
With “start date” select the cell with the start date inside. And with “months” select how many months later you want to have the date (last date of the month).
And obviously you can copy this formula to the right. This with shortcut ctrl R (copy right), and before the copying you need to “shade” the area for copying with holding “shift”.
EOMONTH also logically takes “leap years” into account.
(Danielle Stein Fairhurst, 2019)
Working with dates: Continued
Another very handy function is for example the month function. This function returns the month number of a specific date.
So it gives the month number in which a certain date falls.
You can use this info for example for summarising raw data. Think for example of sales you get on many dates.
With typing in =MONTH and then select a certain date, the function will give you the month number.
And in a practical situation you can then use a SUMIF function (see precious blog) to sum the sales for every month. And this can then be nicely shown in a graph.
By the way, when you have a certain date given, with shortcut ALT, H, N, and then press “down”, you can select “short date” and “long date”, depending on how you like to show the dates in your tab!
(Danielle Stein Fairhurst, 2019)
Net present value (NPV)
NPV is the value of the expected future cash flows of an investment calculated to today in for example euros.
And we discount these cash flows back with a “discount rate”, and this is often the WACC (weighted cost of capital). Check out my previous blogs on WACC since I have written many blogs on this topic.
In excel we can build the NPV formulas manually, but we can also use the build in function.
The latter can be done as follows:
Type in =NPV tab, and then go with ctrl A to the box with function arguments. And here you “jump out” with F2 (with Fn for certain laptops) and select the cell with inside the WACC.
And then for “value1” select the range of which you want to have the NVP. Again, jump out with F2 and then select the range with holding shift.
And there it is! ??
(Danielle Stein Fairhurst, 2019)
Internal rate of return (IRR)
The IRR is the return of a project or investment which gives a NPV of zero.
With financial sponsors, so private equity parties, this IRR is very important. Since with a LBO model (leveraged buyout) we want to know the IRR (in combination with the credit statistics) of an acquisition.
To calculate the IRR of a range of cash flows, act as follows:
Type in =IRR tab, and then ctrl A to get into the “function arguments”. Then jump out with F2 and select the range of cash flow with holding shift.
And we leave the box “guess” blanc for now. I will get back to this later in this sequence of blogs.
And then the IRR is calculated. Once you start to know these formulas, you can type them in straight away without using the “function arguments”.
(Danielle Stein Fairhurst, 2019)
Loan calculations
The most common form of calculating loan repayments is to use an “amortisation schedule”.
Within this method regular (fixed) repayments of equal value are made over the term of the loan. And inside sits an interest component and a principal component.
To calculate the payment of both interest and principal use the PMT (payment) function in excel.
The formula is:
Repayment = loan amount * interest * (1 + interest)^N / (1 + interest)^N – 1)
You can do this on a calculator in order to check it, but excel does this very fast with the PMT function.
And here you need to give in rate (interest), number of repayments and principal amount.
So this is the total amount of principal and interest.
And with I-PMT (interest payment) and P-PMT (principal payment) you can also calculate the separate amounts for interest and principal.
With this amortised schedule, in the end interest is getting less and less since you are paying down the debt (less debt is less interest).
And then the rest of the “fixed/ regular payment” is seen as paying back principal, so this amount is then by definition going up yearly (because of declining interest).
(Danielle Stein Fairhurst, 2019)
Source used for this blog
The source that I have used is the following brilliant book:
· Using Excel for Business and Financial Modelling: A practical guide – 3rd edition (2019). Author: Danielle Stein Fairhurst. Wiley.
This is one of the best books around on financial modelling!
When you are a corporate finance professional I really recommend you to read & study the book.
And because of this I have decided to use it as the main handbook in my 4-day training “Financial Modelling in Excel”.
All the participants who register receive an original hardcopy of the book.
Under here you can find my previous blogs (about 40) on valuation:
Earlier blogs on “net debt” (cash & debt free)
Article 1: Valuation: Introduction to "net debt" (cash & debt free)
https://www.dhirubhai.net/pulse/valuation-introduction-net-debt-cash-free-joris-kersten-msc-bsc-rab/
Article 2: Valuation: Net debt (cash & debt free)
https://www.dhirubhai.net/pulse/valuation-net-debt-cash-free-joris-kersten-msc-bsc-rab/
Article 3: Valuation: Adjusted net debt – Cash like items
https://www.dhirubhai.net/pulse/valuation-adjusted-net-debt-cash-like-items-kersten-msc-bsc-rab/
Article 4: Valuation: Adjusted net debt – Debt like items
https://www.dhirubhai.net/pulse/valuation-adjusted-net-debt-like-items-joris-kersten-msc-bsc-rab/
Earlier blogs on Financial Modelling
Article 1: Financial Modelling in Excel: Circular references, interest calculations and iterations
https://www.dhirubhai.net/pulse/financial-modelling-excel-circular-references-kersten-msc-bsc-rab/
Article 2: Excel basics for Finance: SUM, MAX, MIN, AVERAGE, IF, cell referencing, named ranges
https://www.dhirubhai.net/pulse/excel-basics-finance-sum-max-min-average-cell-named-joris/
Article 3: Excel for Valuation: COUNTIF, VLOOKUP, INDEX and MATCH
https://www.dhirubhai.net/pulse/excel-valuation-countif-vlookup-index-match-kersten-msc-bsc-rab/
Article 4: Excel for Business Valuation: OFFSET, FORECAST and CHOOSE
https://www.dhirubhai.net/pulse/excel-business-valuation-offset-forecast-choose/
Earlier blogs on “various topics”
Article 1: Financing a M&A transaction: An introduction
https://www.dhirubhai.net/pulse/financing-ma-transaction-introduction-joris-kersten-msc-bsc-rab/
Article 2: Valuation: How to adjust for “Operating Lease” (under Dutch GAAP)
https://www.dhirubhai.net/pulse/valuation-how-adjust-operating-lease-under-dutch-gaap-joris/
Article 3: M&A closing mechanisms: Locked Box & Completion Accounts
https://www.dhirubhai.net/pulse/ma-closing-mechanisms-locked-box-completion-accounts-joris/
Article 4: Scoping a financial model built primarily for business valuation:
https://www.dhirubhai.net/pulse/scoping-financial-model-built-primarily-business-joris/
Article 5: Consolidation of M&A targets and Purchase Price Allocation (PPA)
https://www.dhirubhai.net/pulse/consolidation-ma-targets-purchase-price-allocation-joris/
Earlier blogs on “bonds”
Article 1: Bonds - An introduction
https://www.dhirubhai.net/pulse/corporate-finance-bonds-introduction-joris-kersten-msc-bsc-rab/
Article 2: Bonds & Bond Markets
https://www.dhirubhai.net/pulse/bonds-bond-markets-corporate-finance-joris-kersten-msc-bsc-rab/
Article 3: Bonds, Rating Agencies and Credit Ratings
https://www.dhirubhai.net/pulse/bonds-rating-agencies-credit-ratings-joris-kersten-msc-bsc-rab/
Earlier blogs on “Valuation & funding of start-ups”
Article 1: Valuation & funding of start-ups - Funding rounds
https://www.dhirubhai.net/pulse/valuation-funding-startups-rounds-joris-kersten-msc-bsc-rab/
Article 2: Startup valuation: Pre-money and post-money valuation
https://www.dhirubhai.net/pulse/startup-valuation-pre-money-post-money-joris-kersten-msc-bsc-rab/
Article 3: Valuation methods for Startups (early stage) – Part 1
https://www.dhirubhai.net/pulse/valuation-methods-startups-early-stage-part-1-kersten-msc-bsc-rab/
Article 4: Valuation methods for Startups (early stage) – Part 2
https://www.dhirubhai.net/pulse/valuation-methods-startups-early-stage-part-2-kersten-msc-bsc-rab/
Article 5: Startups in Silicon Valley: The beginning – Part 1
https://www.dhirubhai.net/pulse/startups-silicon-valley-beginning-part-1-joris-kersten-msc-bsc-rab/
Earlier blogs on the “cost of capital”
Article 1: Valuation & Betas (CAPM)
https://www.dhirubhai.net/pulse/valuation-betas-capm-joris-kersten-msc-bsc-rab/
Article 2: Valuation & Equity Market Risk Premium (CAPM)
https://www.dhirubhai.net/pulse/valuation-equity-market-risk-premium-capm-joris-kersten-msc-bsc-rab/
Article 3: Is the Capital Asset Pricing Model dead ? (CAPM)
https://www.dhirubhai.net/pulse/capital-asset-pricing-model-dead-capm-joris-kersten-msc-bsc-rab/
Article 4: Valuation & the cost of debt (WACC)
https://www.dhirubhai.net/pulse/valuation-cost-debt-wacc-joris-kersten-msc-bsc-rab/
Article 5: Valuation & Capital Structure (WACC)
https://www.dhirubhai.net/pulse/valuation-capital-structure-wacc-joris-kersten-msc-bsc-rab/
Article 6: International WACC & Country Risk – Part 1
https://www.dhirubhai.net/pulse/valuation-international-wacc-country-risk-part-1-joris/
Article 7: International WACC – Part 2
https://www.dhirubhai.net/pulse/valuation-international-wacc-part-2-joris-kersten-msc-bsc-rab/
Article 8: Present Values, Real Options, the Dot.com Bubble
https://www.dhirubhai.net/pulse/valuation-present-values-real-options-dotcom-bubble-joris/
Article 9: Valuation: Different DCF & WACC techniques
https://www.dhirubhai.net/pulse/valuation-different-dcf-wacc-techniques-joris-kersten-msc-bsc-rab/
Article 10: Valuation of a company abroad
https://www.dhirubhai.net/pulse/valuation-company-abroad-joris-kersten-msc-bsc-rab/
Article 11: Valuation: Illiquidity discounts, control premiums and minority discounts
https://www.dhirubhai.net/pulse/valuation-illiquidity-discounts-control-premiums-joris/
Article 12: Valuation: Small firm premiums
https://www.dhirubhai.net/pulse/valuation-small-firm-premiums-joris-kersten-msc-bsc-rab/
Earlier blogs on “Business valuation to Enterprise Value”
From June until August I have written the following blogs on valuation:
1) Leveraged Buyout (LBO) Analysis:
https://www.dhirubhai.net/pulse/leveraged-buyouts-lbos-joris-kersten-msc-bsc-rab/
2) M&A Analysis – Accretion/ Dilution:
https://www.dhirubhai.net/pulse/ma-model-accretion-dilution-joris-kersten-msc-bsc-rab/
3) Discounted Cash Flow Valuation:
https://www.dhirubhai.net/pulse/discounted-cash-flow-valuation-dcf-joris-kersten-msc-bsc-rab/
4) Valuation Multiples 1 – Comparable Companies Analysis:
https://www.dhirubhai.net/pulse/valuation-multiples-1-comparable-companies-analysis-joris
5) Excel Shortcuts & Business Valuation:
https://www.dhirubhai.net/pulse/excel-shortcuts-business-valuation-joris-kersten-msc-bsc-rab
6) Valuation Multiples 2 – Precedent Transaction Analysis:
https://www.dhirubhai.net/pulse/valuation-multiples-2-precedent-transaction-kersten-msc-bsc-rab
Earlier blogs on Wall Street
Article 1: Wall Street – A general introduction
https://www.dhirubhai.net/pulse/wall-street-general-introduction-joris-kersten-msc-bsc-rab/
Article 2: Wall Street – The Federal Reserve banking system
https://www.dhirubhai.net/pulse/wall-street-federal-reserve-banking-system-kersten-msc-bsc-rab/
Training calendar
My training in “Business Valuation & Deal Structuring” this March 2020 in The Netherlands is rescheduled due to the corona virus.
But my NEW training calendar in The Netherlands is as follows:
1. 17, 18, 19, 20 and 22, 23 June 2020: 6 days - Business Valuation & Deal Structuring. Location: Uden/ The Netherlands;
2. 24, 25, 26, 27 and 29, 30 June 2020: 6 days - Business Valuation & Deal Structuring. Location: Uden/ The Netherlands;
3. 28, 29, 30, 31 October 2020 + 2, 3 November 2020: 6 days - Business Valuation & Deal Structuring. Location: Amsterdam Zuidas/ The Netherlands;
4. 16, 17, 18, 19 November 2020: 4 days - Financial Modelling in Excel. Location: Amsterdam Zuidas/ The Netherlands.
All info on these open training sessions can be found on: www.joriskersten.nl
And 130 references on my training sessions can be found on: www.joriskersten.nl