We encourage all users to understand how our calculations work, as you'll probably have to explain this to your auditors! 

Key areas of the calculation are:

First, to access the Audit Schedule, click the on the applicable agreement and you'll then be taken to the overview tab:

Then scroll down and click "Download Audit Schedule"

For a full calculation example of how to calculate every input in the Audit Schedule report, refer here. This spreadsheet highlights:

To recalculate the agreement you must use the "XNPV" function in excel: There are several ways to present value a figure in excel. Refer here for more details. The NPV formula in excel is useful for most scenarios when present valuing the net cash flow periodically; however, the disadvantage of using the NPV formula is, the formula is unable to adapt to any non-periodic changes. For example, a modification that occurred on a date that is different from the payment date. On the other hand, the XNPV formula can adapt any non-periodic payment, such as a one-off payment on any day other than the regular payment date. 

Given the Excel file provides the data of all upcoming lease payments (scheduled & one-off), the quickest way is to filter for that data, add the applicable discount rate, and you can present the value of those payments. 

When the Excel file is exported, you will see:

The first step to get the XNPV formula to work is to fill all the blank cells in column C with zero amounts:

Once you have filtered out the zero amounts, copy and paste the date and payments to a new sheet:

Input the same discount rate used in Cradle, and you will get the net present value of the lease liability at 2022-1-1:

This agrees to the lease liability before payment on 2022-1-1.

Hint

Make sure you don't include the MGP or ARO payment in the XNPV calculation of the lease liability.

Another cross-check you can run the journals in Cradle from 2022-1-1 to 2022-1-31:

When modification accounting occurs this will update all future cash flows based on the date effective entered. As a result, if you recalculate any lease liability value before the modification, you'll need to update those cash flows to what they were previously before the modification.

Refer to the example here of how to do that.

In this example, there is a make-good provision of $5,000 at a discount rate of 7%. The commencement of the lease is 2019-1-1, with an end date of 2019-12-31. To recalculate the MGP:

Step 1) Extract the Excel and add some additional columns.

As you can see, an additional column has been added to the present value of the $5k, which is due to be paid on December 31, 2026.

Step 2) Input the applicable for XNPV formula and recalculate

We highly recommend you send this article to your auditors. They will need to obtain sufficient audit evidence over your lease accounting balances. The way they'll do that is with substantive testing. The audit schedule reports provide all the granularity an auditor needs to:

This help article should show you how Cradle's numbers are calculated and how to recalculate them if you like.

#how to calculate the lease liability in Cradle

#how to calculate the ROU asset in Cradle

#how Cradle's calculations work
#npv
#audit schedule

​