How to Use the Excel PV Function for IFRS 16 Calculations

The Excel PV function is the easiest way to make your IFRS 16 calculations more simple and efficient.

By using the PV function, we are able to calculate the present value of the IFRS 16 lease liability in an instant.

In order to to use the function, you will need to know the following information about the lease that you are accounting for under IFRS 16:

1. The interest rate on the lease
2. The amount of each repayment on the lease
3. The number of payments remaining

For our example we will use the information in the screenshot below:

These are the monthly payments on the lease of a building, which is a common scenario encountered by many businesses.

Before we input these figures in to PV formula, we have to understand one key thing:

Although we have the monthly payment amount, the interest rate on the lease will be an annual rate in almost every situation. Therefore, we must divide the interest rate by 12 in all of our workings to get the monthly interest rate.

Inputting the figures into the Excel PV Function

The first step we need to take is to set up the formula in excel.

To do this we simply select an empty cell and type “=PV” followed by an open bracket. When typed into excel, it should look like this:

When using the function for IFRS 16 calculations we will only need the first three arguments. We have listed these three arguments below and explained what each one means.

• Rate – here we will enter the interest rate on the lease. This is the rate per period so as discussed above, we will divide the interest rate by 12 before entering as the payments are made in monthly periods.
• Nper – This arguments requires the user to enter the number of periods in the lease. In our example we have 48 months remaining so here we will enter “48”.
• Pmt – This is where we will enter the amount of each period’s payment. We already know the monthly payment amount so we will simply enter £1,000 here. However, if we only knew the annual amount we would have to divide by 12 as this argument requires the payment per period! We will enter this as a negative figure as it is a payment we are making rather than receiving.

The other 2 arguments are optional and are not required for IFRS 16 calculations. Therefore we will simply enter nothing for these arguments.

Now we will enter the information into the PV function and once complete, the formula should look like this:

=PV(3%/12,48,-1000)

Remember to end the formula with a closed bracket. Then we can press enter and the formula should return the following result:

We can see that the present value of this lease liability is £45,178.69.

This figure can now be used for the remainder of the required IFRS 16 Calculations. For more information on the other journal entries required for IFRS 16, please see our guide here.

Key Points to Remember

Although the excel PV function is simple to use, there are some things you must always remember to do if you are using it for IFRS 16 calculations:

• Always remember to divide the interest rate and payment amount by 12 if you are given annual figures (divide by 4 if payments are made quarterly or simply leave as annual amount if the payments are made annual, although this is rare.
• Always enter the “Pmt” argument as a negative figure. If you don’t, the value returned will be a negative which could mess up other calculations further down the line.