3503999 - Lease Valuation Cash Flow Report - How to calculate a RoU Asset Opening value/ Lease Contract Lease Liability Opening Balance

Symptom

The Lease Contract Valuation Cash Flow report calculates an unexpected ROU opening balance/Lease Opening Balance for a lease contract.

To calculate the lease contract's present value (PV) you applied the discounted cash flow formula. The resulting lease liability amount/ ROU opening balance is different to the mentioned amounts displayed in the cash flow report.

Environment

SAP Business ByDesign

Reproducing the Issue

Lease Contract ABC

Start Date:	01.01.YYYY
End Date:	31.12.YYYY
Lease Term in Month:	12
Interest:	5%
Lease Installments:	1000,00 LC (Local Currency)
Recurrence:	Once, every month
Payment Form:	In Arrears

Apply the discounted cash flow formula: Payment / (1 + Interest Rate):

For each period you calculate a lease liability of 952,38 LC: 1000,00 LC / (1+0.05), resulting in a total lease liability/ ROU opening balance of 11.428,56 LC (952,38 LC x 12).

However, the lease contract cash flow report calculates a different ROU opening balance: 11.685,32 LC. You wonder about the formula behind this value.

Cause

1. Act/360:

In SAP Business ByDesign the net present value, interest and depreciation are calculated daily based on the actual/360 day count convention, using the actual number of days in a month and 360 days in a year to calculate interest payments.

Example:

Period 1: 01.01.YYYY to 31.01.YYYY:

Number of days 30.

Act/360: 0,083333 (30/360).

2. Compound Interest Calculation - Discount Factor Calculation:

The formula for discounting and interest calculation uses compound interest (exponential interest calculation): PV= Payment x Discount Factor (DF).

The easiest way to calculate the discount factor is by applying the following formula in MS Excel:

DF= 1/POWER((1+interest rate),number of days/360).

Example:

Interest rate: 5%, i.e. 0,05

Month: from 01.01.2024 to 31.01.2024: 30 days

DF= 1/POWER((1+0.05),30/360)= 0.995942407

3. Present Value - Lease Liability - ROU Opening Balance Calculation (at its simplest form, not regarding additional service charges, initial cost etc.) :

To calculate the present value/the lease liability opening balance on which the ROU opening balance is based on use the following formula in MS Excel:

PV=ROUND(Payment amount*DF),number of digits)*

*Use the ROUND formula according to the decimal places of the used currency (for example, enter 0 for JPY, which uses no decimal places; enter 2 for EUR which uses 2 decimal places).

Resolution

To calculate the ROU opening balance use MS Excel and apply the formulas as above/below:

Days: =days(due date,01/01/YYYY)
act/360: days/360
Discount Factor: 1/POWER((1+interest rate),number of days/360)
PV/ROU opening balance: ROUND(Payment amount*DF),number of digits)

Due Date	Number of Days	act/360	Interest rate	Discount factor	Payment Amount	PV
01/01/YYYY	0	0	0.00	0	0	0
31/01/YYYY	30	0.083333333	0.05	0.995942407	1000	995.94
29/02/YYYY	59	0.163888889	0.05	0.992035718	1000	992.04
31/03/YYYY	90	0.25	0.05	0.987876547	1000	987.88
30/04/YYYY	120	0.333333333	0.05	0.983868147	1000	983.87
31/05/YYYY	151	0.419444444	0.05	0.979743219	1000	979.74
30/06/YYYY	181	0.502777778	0.05	0.97576782	1000	975.77
31/07/YYYY	212	0.588888889	0.05	0.971676853	1000	971.68
31/08/YYYY	243	0.675	0.05	0.967603038	1000	967.6
30/09/YYYY	273	0.758333333	0.05	0.963676899	1000	963.68
31/10/YYYY	304	0.844444444	0.05	0.959636624	1000	959.64
30/11/YYYY	334	0.927777778	0.05	0.955742809	1000	955.74
31/12/YYYY	365	1.013888889	0.05	0.951735798	1000	951.74
				SUM/MROU/Lease Liabilty Opening Balance		11685.32

Keywords

ROU opening balance unclear, Lease Opening Balance, Lease Contract Cash Flow , KBA , AP-ACC-LM , Accounting Lease Management , Problem

Product

SAP Business ByDesign all versions