Interest rate of a lease

[TE]

Hi!

Im trying to calculate the interest rate of a lease with PowerBI (essentially the Excel RATE -function). 

My columns are:

-Monthly Lease Payment

-Total Leasing Capital

-Duration (in months)

-Advanced Payment

-Residual Value

If I understand correctly, since the payments are periodic, I have to use XIRR formula like in the post below. 

https://community.powerbi.com/t5/Desktop/Excel-Rate-function-to-build-in-Power-BI/m-p/422743

One way would be to modify the GENERATESERIES formula in order to accommodate the advanced payment in the first period and and residual value in the last. How would I go about doing this?

Any other suggestions?

Thanks!

TE 

[v-chuncz-msft · Accepted solution]

@TE 

It seems that you may directly subtract the advanced payment from the total.