Excel functions for mortgage

February 5, 2024March 14, 2022 by Liangyao TANG

Excel functions for mortgage

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.

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.

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.

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).

How to compute the IRR in Excel

November 13, 2021 by Jérémy PAULEN

How to compute the IRR in Excel

Jeremy PAULEN

In this article, Jérémy PAULEN (ESSEC Business School, Global Bachelor of Business Administration, 2019-2023) explains everything about the IRR function in Excel, which is used to compute the internal rate of return of a series of cash flow to evaluate the financial performance of an investment in relative terms.

What is the IRR?

The IRR represents the internal rate of return of an investment. It is closely related to the net present value (NPV) of the investment as the IRR is the discount rate that makes the NPV equal to zero.

Consider an investment represented by a series of cash flows CF₀, CF₁, CF₂, …, CF_T, which take into account the revenues and expenses of the project computed or forecasted at time 0 leading to capital inflows and outflows for the firm. The NPV of this investment is given by:

where r is the discount rate that takes into account the risk of the project.

The IRR corresponds to the value of the discount rate for which the NPV is equal to 0:

The IRR is the solution of a non-linear equation:

Note that this equation may have one solution, several solutions or no solution according to the sequence of cash flows.

The internal rate of return (IRR) is an important indicator in the decision-making process as it measures the financial performance of a project. The IRR is a relative measure as its unit is a percentage. The NPV is an absolute measure as its unit is the euro, the dollar, etc.

It makes it possible to measure the future financial performance of a project or a company. The higher the IRR is, the more interesting it is to launch the project.

The IRR can therefore be used in the case of a choice to be made between different investment perspectives, but also to evaluate the company’s share buyback programs.

A limit of using the IRR method is that it does not consider the size of a project. Cash flows are simply compared to the amount of capital outlay generating those cash flows. In other words, considering two projects A and B, the IRR of A may be lower than the IRR of B, while the NPV of A may be higher than the NPV of B.

The IRR function in Excel

How to use the IRR function in Excel?

In Excel, you can get the IRR function in the “Formulas” tab.
You can also type “= IRR (value, [guess])” in the cell where you want to compute the IRR.

The IRR function uses the following arguments:

Values: The cash flow series. Cash flows include investment values and net income.
Guess: a number guessed by the user that is close to the expected internal rate of return

Example

Example: consider a new factory modeled by the following series of cash flows:

CF₀ = -$50,000 (initial cost)
CF₁ = +$5,000 (net cash flow in year 1)
CF₂ = +$8,000 (net cash flow in year 2)
CF₃ = +$13,500 (net cash flow in year 3)
CF₄ = +$18,800 (net cash flow in year 4)
CF₅ = +$20,500 (net cash flow in year 5)

Excel file to compute the IRR of a series of cash flows

You can download below a short video which illustrates how to compute the IRR of a series of cash flows with Excel.

Useful resources

Microsoft IRR function

About the author

The article was written in November 2021 by Jérémy PAULEN (ESSEC Business School, Global Bachelor of Business Administration, 2019-2023)

How to compute the present value of an asset?

May 15, 2021 by William LONGIN

How to compute the present value of an asset?

In this article, William LONGIN (EDHEC Business School, Global BBA, 2020-2024) elaborates on the concept of Present Value.

What is present value?

The present value (PV) of an asset is usually computed as the value of the stream of its future cash flows discounted at a given rate of return. In the calculation of the present value of an asset, there are two inputs: the expected future cash flows generated by the asset and the discount rate which takes into account the risk on the future cash flows.

The discounting operation takes into account that an amount of money today is worth more than the same amount tomorrow. €100 lent or invested today at an interest rate of 10% is equal to €110 in one year. If you are to receive €100 in one year, you can borrow €90.90 to get this money today. In one year, you will have to repay the amount borrowed €90.90 and the interests €9.10, that is a total cash flow of €100 (that you will pay with the €100 that you are supposed to receive in one year). This refers to the concept of time value of money, best illustrated by the following quote: “Remember that time is money” – Benjamin Franklin (1748).

How is present value computed?

The formula for the present value (PV) of a cash flow occurring at time t, denoted by CF_t, discounted with the discount rate r, is given by:

Present value of a cash flow

The above formula can be used to illustrate the time value of money. What is the present value of €100 obtained in 1 year, 5 years and 10 years? The table below gives the present value by discounting €100 obtained in 1 year, 5 years and 10 years with a discount rate of 10%. Present value shows that money received in the future is not worth as much as an equal amount received today.

This formula can be generalized for a series of cash flows, CF_t, from t=1 to t=T:

Present value of a series of cash flows

Application 1: Computation of the present value of a stock

The concept of present value can be applied to value a stock.

For a stock, the series of cash flows corresponds to the dividends paid by the firm to its stockholders at the end of each period t (DIV_t) and the price P_T at which the stock is supposed to be sold at time T (the horizon of the investor). The present value (PV) is then equal to the discounted value of this series of cash flows at the discount rate r.

Present value of the series of cash flows for a stock

Let us take the example of the valuation of a stock paying a dividend every year. The expected cash flows for dividends is €4 in Year 1, €4 in Year 2, €5 in Year 3, €5 in Year 4, €7 in Year 5 (end of year). The expected resale price in Year 5 is €110 (end of year). Using a discount rate of 10%, the present value of this stock is equal €94.41.

In practice, there are three steps to compute the present value of a stock:

Step 1: Estimate the expected value of future dividends and of the future price
Step 2: Estimate the discount rate given the risk characteristics of the stock
Step 3: Compute the present value

Application 2: Computation of the present value of a bond

The concept of present value can be applied to value a bond. For a fixed-rate bond, the series of cash flows corresponds to the interest paid at the end of each period t (coupon C_t) and the principal value (V) reimbursed at maturity T. The present value (PV) is equal to the discounted value of the series of cash flows at the discount rate r.

Present value of the series of cash flows for a bond

Let us take the example of the valuation of a bond with a nominal value of €1,000, a nominal interest rate of 5%, payment of interests on a yearly basis at the end of the year, and maturing in 5 years. The annual interest is computed as follows: 0.10*1,000 = €100. The last payment corresponds to the interest of the last year (€50) and the reimbursement of the initial capital (€1,000). The series of cash flows is then given by +50, +50, +50, +50, +1,050. Using a discount rate of 5%, the present value of this bond is equal €1,000.

In practice, there are three steps to compute the present value of a bond:

Step 1: Find the characteristics of a bond to compute the cash flows associated to the bond
Step 2: Estimate the discount rate given the risk characteristics of the bond
Step 3: Compute the present value

How to properly compute cash flows?

The future cash flows may be certain or uncertain. When the future cash flows are uncertain, the expected value of the future cash flows, computed as the average of the possible values weighted by their probability, enters the formula for the present value.

Who is using present value?

In financial markets, it is used by traders and investors to estimate the value of financial securities like stocks and bonds.

In the asset management industry, it is also used by asset managers in investment firms (like private equity) to value firms to buy or sell.

In the corporate world, it is used by project managers to estimate the value of the future investments by the firm.

In the accounting context, it is used by accountants to compute the model value of some elements of the balance sheet according to the International Financial Reporting Standards (IFRS).

So, we can see that the concept of present value is useful to a large range of professionals needing to calculate and estimate the value of assets.

▶ William LONGIN My experience as a junior financial analyst at ACE

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

▶ Jérémy PAULEN How to compute the IRR in Excel

▶ Sébastien PIAT Simple interest rate and compound interest rate

About the author

Article written in May 2021 by William LONGIN (EDHEC Business School, Global BBA, 2020-2024).