I faced an issue with the XNVP equation if the last payments were due not ordinary. The last payment date is August 1, Y2 and the lease expires July 31, 3. The interest is always calculated till August Y2 only. Please advise
This article goes through how to determine the discount rate of a lease www.cradleaccounting.com/insights/how-to-calculate-the-discount-rate-for-a-lease-under-under-asc-842
Hi Andrew, at day 0 the interest rate is applied. That that's XNPV before any payments. Refer here www.cradleaccounting.com/insights/how-to-calculate-a-daily-lease-liability-amortization-schedule-in-excel for more details.
That's probably due to not correctly converting the discount rate to a daily amount. More information on the formula used to do that here www.cradleaccounting.com/insights/how-to-calculate-the-lease-liability-and-right-of-use-asset-for-an-operating-lease-under-asc-842#step-4-calculate-the-unwinding-of-the-lease-liability
That's a great question! The accounting standards are silent on this level of detail. We would recommend after payment; however, you could justify either.
It is the XNPV function in Excel. For more details on that, refer to this article on Cradle's website - How to Calculate the Present Value of Future Lease Payments found at www.cradleaccounting.com/insights/how-to-calculate-the-present-value-of-future-lease
I faced an issue with the XNVP equation if the last payments were due not ordinary. The last payment date is August 1, Y2 and the lease expires July 31, 3. The interest is always calculated till August Y2 only. Please advise
Very useful, thanks!!
I have used the tool and it is really an amazing tool , I highly recommend it .
Great to hear!
How did you get the discount rate figure please?
This article goes through how to determine the discount rate of a lease www.cradleaccounting.com/insights/how-to-calculate-the-discount-rate-for-a-lease-under-under-asc-842
very good method, thank you
Question if you are doing the advance payment then at day 0 should not we apply the interest rate?
Hi Andrew, at day 0 the interest rate is applied. That that's XNPV before any payments. Refer here www.cradleaccounting.com/insights/how-to-calculate-a-daily-lease-liability-amortization-schedule-in-excel for more details.
In column F, I don't get 3.99. I get 4.30. Is there formatting I can change to correct this?
That's probably due to not correctly converting the discount rate to a daily amount. More information on the formula used to do that here www.cradleaccounting.com/insights/how-to-calculate-the-lease-liability-and-right-of-use-asset-for-an-operating-lease-under-asc-842#step-4-calculate-the-unwinding-of-the-lease-liability
how can i have the same features as you and colours i excel ?
These are the default settings when using the Mac version of excel.
great video, i was wondering should interest be calculated post payment or pre payment?
That's a great question! The accounting standards are silent on this level of detail. We would recommend after payment; however, you could justify either.
what about ROU Calcualtion ?
Hi Anwar, for more information on how to calculate the right of use asset, refer to cradleaccounting.com/insights
What is the Equation of daily discount rate?
It is the XNPV function in Excel. For more details on that, refer to this article on Cradle's website - How to Calculate the Present Value of Future Lease Payments found at www.cradleaccounting.com/insights/how-to-calculate-the-present-value-of-future-lease
Anyone how to calculate if given lease incentives 50% discount for 3 month
You will reduce those lease payments by 50%. This will then reduce the present value amount of the lease liability.