Excel functions for mortgage

Excel functions for mortgage

 Liangyao TANG

In this article, Liangyao TANG (ESSEC Business School, Master in Strategy & Management of International Business (SMIB), 2021-2022) explains the functions in Excel that are useful to study a mortgage. Mastery of Excel is an essential skill nowadays in financial analysis and modelling tasks. Proficiency in using Excel formulas can help analysts quickly process the data and build the models more concisely.

Mortgage

A mortgage is the type of loan used in real estate, vehicles, and other types of property purchasing activities. There are two parties in the mortgage contract: the borrower and the lender. The contract sets the terms and conditions about the principal amount, interest rate, interest type, payment period, maturity, and collaterals. The borrower is contracted to pay back the lender in a series of payments that contains part of the principal as well as the interests before the maturity date.

The mortgage is also subject to different terms according to the bank’s offers and macroeconomic cycle. There are two types of interest rates: the fixed-rate loan and the floating (variable) rate loan, in which the interest rate is a pre-determined rate (at the beginning of the period) and post-determined rate (at the end of the period).

Example of repayment schedule.
Example of repayment schedule

In this post, I will use the following example: a mortgage of $300,000 for property purchasing. The mortgage specifies a 5% fixed annual interest rate for 30 years, and the borrower should pay back the loan on a monthly basis. We can use Excel functions to calculate the periodic (monthly) payment and its two components, the principal repaid and the interests paid for a given period. The calculations are shown in the sample Excel file that you can download below.

Download the Excel file for mortgage

PMT

The “PMT” (Payment) Excel function calculates the periodic mortgage payment.

The periodic repayment for a fixed-rate mortgage includes a portion of repayment to the principal and an interest payment. Since the mortgage has a given maturity date, the payment is calculated on a regular basis, for example, every month. All repayments are of equal amount throughout the loan period.

The mathematical formula for the periodic mortgage payment is:

Formula for the periodic mortgage payment

With the following notations:

  • PMT: the payment
  • P: the principal value
  • r: the interest rate
  • N: the total number of periods

The repayment schedule is a table which gives the periodic payment, and the principal repaid and the interests paid for a given period. It can be a large table. For example, the repayment schedule of a loan with 30 year maturity and monthly payment has 180 lines. In formal terms, the payment schedule would be:

Repayment schedule of a mortgage

The repayment schedule shows the payment amount of each period, and the remaining principal balance after each payment. The ‘P’ represents the principal amount at the beginning of the mortgage, and the remaining principal is subjective to an (1+r) times interests at each period. The remaining principal is the principal balance from last period minus the current payment. Therefore for period 1, the remaining balance is equal to P(1+r), which is the principal with one year of interest, minus the PMT value, which is the payment of the current period.

The syntax for the Excel function to calculate the periodic payment is: PMT(rate, nper, pv, [fv], [type]).

With the following notations:

  • PMT: the periodic payment of the loan
  • Nper: the total number of periods of the loan
  • PV : the principal (present value) of the loan
  • [fv]: the future value of the loan (optional parameter). Default equal to 0
  • [type]: when payments are due (optional parameter). 0 = end of period, 1 = beginning of period. Default is 0

The function is used explicitly in the case of a fixed interest rate to compute the (constant) periodic payment.

The PMT function will calculate the loan’s payment at a given level of interest rate, the number of periods, and the total value of the loan for principals at the beginning of the period (principal + interest).

When using the function, it is essential to always align the time unit of the interest rate and the unit of Nper. If the mortgage is compounding on a monthly basis, the number of periods should be the total number of months in the amortization, and the rate should be the monthly interest rate, which equals the annual rate divided by 12. . In the above example, the interest should be paid in a monthly basis, therefore the number of period (Nper) is equal to 12 month x 30 year = 360 periods. Since the annual interest rate is 5%, the monthly interest rate would equal to 5% divide by 12, which is 0.42% per month.

IPMT and PPMT

To supplement on the information about the monthly payment, we can also use the function IPMT and PPMT to calculate the principal repaid and the interest rate paid for a given period.

IPMT

IPMT is the Excel function that calculates the interest portion in each of the periodic payment.

The syntax of the Excel function to calculate the interest portion of the periodic payment is: IPMT(rate, per, nper, pv, [fv], [type]).

With the following notations:

  • IPMT: interest payment
  • rate: interest rate
  • per: current period number
  • nper: total number of periods
  • pv: present value
  • [fv]: the future value of the loan (optional parameter). Default equal to 0
  • [type]: when payments are due (optional parameter). 0 = end of period, 1 = beginning of period. Default is 0

The rate refers to the periodic interest rate, while the “nper” refers to the total number of payment periods, and the “per” refers to the period for which we want to calculate the interest.

PPMT

PPMT is the Excel function that calculates the principal portion of a periodic payment.

The syntax of the Excel function to calculate the principal portion of a periodic payment is: PPMT(rate, per, nper, pv, [fv], [type]).

With the following notations:

  • PPMT: principal payment
  • rate: interest rate
  • per: current period number
  • nper: total number of periods
  • pv: present value
  • [fv]: the future value of the loan (optional parameter). Default equal to 0
  • [type]: when payments are due (optional parameter). 0 = end of period, 1 = beginning of period. Default is 0

Those of the results should be consistent with the amortization schedule shown above. The principal repayment should equal to PMT per period minus the interest rate paid (IPMT).

RATE

Contrarily, if the user is given the periodic payment amount information and wants to find out about the interest rate used for the calculation, he/she can use the RATE function in Excel.

The syntax of the Excel function to calculate the rate is: RATE(nper, pmt, pv, [fv], [type], [guess]).

With the following notations:

  • RATE: the interest rate
  • nper: the total number of payment periods
  • pmt: the constant periodic payment
  • pv: the principal amount
  • [fv]: the future value of the loan (optional parameter). Default equal to 0
  • [type]: when payments are due (optional parameter). 0 = end of period, 1 = beginning of period. Default is 0
  • [guess]: your guess on the rate (optional parameter). Default is 10%

The RATE Excel function will automatically calculate the interest rate per period. The time unit of the interest rate is aligned with the compounding period; for example, if the mortgage is compounding on a monthly basis, the RATE function also returns a monthly interest rate.

Example with an Excel file

The use of the Excel functions PMT, IPMT, PPMT and RATE is illustrated in the Excel file that you can download below.

Download the Excel file for mortgage

Related posts on the SimTrade blog

   ▶ Maite CARNICERO MARTINEZ How to compute the net present value of an investment in Excel

   ▶ William LONGIN How to compute the present value of an asset?

   ▶ Léopoldine FOUQUES The IRR, XIRR and MIRR functions in Excel

   ▶ Jérémy PAULEN The IRR function in Excel

Useful resources

Forbes What is a mortgage

Rocket mortgage Types of mortgage

Ramsey How Do Student Loans Work?

Prof. Longin’s website Echéancier d’un crédit (mortgage calculator in French)

About the author

The article was written in March 2022 by Liangyao TANG (ESSEC Business School, Master in Strategy & Management of International Business (SMIB), 2021-2022).

My experience as a financial analyst at CASIM

My experience as a financial analyst at CASIM

 Liangyao TANG

In this article, Liangyao TANG (ESSEC Business School, Master in Strategy & Management of International Business (SMIB), 2021-2022) discusses the valuation methodologies in private equity and venture capitals based on her experience at the CAS Investment Management Co., Ltd.

CASIM

Chinese Academy of Sciences Holdings Co., Ltd., also referred to as CAS Investment Management Co., Ltd., is the first state-owned asset management company approved by the Assets Supervision and Administration Commission of the State Council (SASAC). Its predecessor is the Chinese National Economic Council-CAS Scientific and Technological Promotion & Economic Development Foundation. On April 12, 2002, CAS investment completed the industrial and commercial registration and was restructured into a limited liability asset management company.

Logo of CAS Investment Management.
Logo of CAS Investment Management
Source: CAS Investment Management.

Since 2008, CAS Investment has carried out Private Equity fund investment business as an institutional investor. It had directly invested in many strategic emerging industries that helped shape China’s technological development. As of the end of 2018, the registered capital of CAS Investment was 5.1 billion RMB, holding more than 50 companies, which mainly distributed in information technology, high-end equipment manufacturing, environmental technology, and new materials. For the past 25 years, CASIM has supported over 300 successful commercialization and industrialization of technological development, some of which have become the industrial bellwethers and have contributed to promoting scientific achievement conversion in China. The Investment portfolio include Cambricon Technologies, Farasis Energy, ThunderSoft Technologies, and SIASUN Robostics.

My Mission

Passionate about creating value for enterprises and gaining a more on-edge understanding of the business world, I carried out a four-month internship as a Financial Analyst in the CAS Investment Management Co., Ltd. During that time, I joined the team and worked together with a Managing Director and a senior analyst. My responsibility is to conduct market research and desktop analysis for the project, which has allowed me to quickly dive into the TMT industry, especially the e-commerce sector. I conducted more than 20 financial statement analyses, operation strategies analyses, and market analyses to evaluate different investment opportunities. The qualitative analysis includes competitor benchmarking, business model analysis, supply chain and distribution analysis. Quantitatively includes building financial models to calculate future cash flow and income stream, and building market models to estimate the potential market size and market share. In addition, I also performed due diligence by conducting more than 50 interviews with industry experts on multiple projects. During the financial due diligence, I utilized Excel VBA to process and analyze ratios in the massive inventory and operation data to verify the authenticity of the target company’s financial performance. Those analytical work have greatly improved my hands-on ability to build market models and identify potential investment risks. I co-delivered the formal investment proposals for distribution to the Investment Committee, an infrequent task given to an intern during the past several years.

Knowledge and skills required

This investment analyst role requires the candidate to have a deep understanding of the business world and the target industry to be able to value the future potential of a business. The candidate also has to have the critical thinking ability to identify the risks and make rational decisions. During this experience, I have significantly reinforced my knowledge in financial statement analysis and market analysis. I also gained a solid improvement on my fast-learning abilities, analytical skills, and logical thinking capacities, which could be very beneficial for me moving forward in my career path.

Key financial indicators to understand valuation

When and what valuation method should be used has always been a matter of constant debate in the financial industry. The valuation methods depend on different stages of development and different rounds of financing of companies. This brings great differences in the estimated value of firms, resulting in no unified framework in the industry. The valuation can be said to be the most difficult part of a project. The valuation methods that are often used in Private Equity and Venture Capital firms include price-earnings ratio (P/E), price-book ratio (P/B), price-earnings growth (PEG), discounted cash flows (DCF), etc. I describe a few of them below.

P/E

The P/E method is one of the most common valuation methods. There are usually two types of price-earnings ratios for listed companies: historical price-earnings ratio (also called Trailing P/E) and forecasted price-earnings ratio (also called Forward P/E). The historical P/E ratio uses the current market value to divide by the earnings of the company in the previous financial year (or earnings in the previous 12 months). On the contrary, the forward P/E ratio uses current market capitalization, divided by the company’s earnings for the current fiscal year (or earnings for the next 12 months). Investors invest in the company’s future and estimate the present value for the company’s future operating capabilities. Therefore, the calculation formula using the price-earnings ratio method is:

Enterprise value = forecast price-earnings ratio × company profit for the next 12 months.

The price-earnings (P/E) ratio can be chosen from those of the comparable companies, their direct competitors, or the average price-earnings ratio of the industry in which the target company is located. The price-earnings ratio mainly depends on the expected growth rate of the company. The company’s valuation at its growing stage usually is much higher than that of a company at its mature stage. Also, it depends on the business’s risk level or the risk tolerance from the investors. A lower price-earnings ratio is used for a risker business because investors would demand a higher return from the investment.

EV/EBITDA

Another commonly used ratio in private company valuation is the EV/EBITDA ratio. The ratio is used to compare the enterprise value (EV) with the Earnings Before Interest, Taxes, Depreciation & Amortization (EBITDA). This gives investors a sense of how many times of the EBITDA they will have to pay if acquiring the target enterprise. Comparing the EV/EBITDA ratio of a company with the industry average and direct rivals also gives investors an idea of a fair target price. This method could leave out the effect of the company’s depreciation, inventory, non-recurring income and expenditure. Therefore, it is a good supplement to the price-to-earnings ratio valuation method.

P/B

The price-to-book (P/B) ratio is the ratio of market value/ tangible net assets value, or the ratio of share price per share to total book value per share. The EV of the firm is the same as discussed above in the P/E ratio section, and the book value is equal to total assets minus the intangible assets and total liabilities. The P/B ratio demonstrated important information about its market value compared to its accounting measures.

The advantages of this method are that the net assets’ data is easy to obtain, and the book value of net assets usually is authentic and less manipulated than net profit. Additionally, even if the company hasn’t been profitable, the price-to-book ratio is rarely negative, so it’s easier to calculate. However, the book value is affected by the choice of accounting policies. If companies implement different accounting standards, the price-to-book ratio will lose comparability. Secondly, the P/B ratio will not be a good measure for those companies with very few fixed assets. Those companies normally have a bigger portion of intangible assets, such as goodwill, intellectual property, brand reputation, and so on, which are not taking into consideration in the book value and could be easily manipulated. For example, for the service providers, media and entertainment producers, and IT companies, the company’s potential has very minimal relevance with its fixed assets. Therefore, the P/B ratio can be meaningless in some cases.

To sum up, the valuation of private enterprises, especially for start-up companies, is a unique and challenging task. The process is usually a combination of scientific computation and some flexibilities. No valuation method is perfect; analysts need to be very flexible and sharp with the method and the supplementary information they use to draw an objective conclusion.

Related posts on the SimTrade blog

   ▶ All posts about Professional experiences

   ▶ Barbero A. Career in finance

   ▶ Ma S. Expeditionary experience in a Chinese investment banking boutique

Useful resources

McKinsey & Company (23/10/2019) Pricing: The next frontier of value creation in private equity

Insider Intelligence (06/01/2022) Financial Services Industry Overview in 2022: Trends, Statistics & Analysis

BVCA Private Equity Explained

About the author

The article was written in March 2022 by Liangyao TANG (ESSEC Business School, Master in Strategy & Management of International Business (SMIB), 2021-2022).