Fair enough. Ran the math your way and the payoff difference was a little over $100 at the time I paid off my lease vs. my estimate. Neither one exactly matches the BMW payoff doc for what it’s worth so there might be another fee built it. Not worth my time to dig through my paperwork to dig up the contract. But I will concede you are right.
1 Like
Does with ccap
1 Like
CLOSE! It’s the present value of the unearned rent charges… So,
remaining payments - present value of the unearned rent charges + RV
2 Likes
This thread smells nerdy and feels like home.
4 Likes
Present value at what rate?
It’s the interest rate implicit in the lease commonly referred to as the actuarial or constant yield rate in lease agreements. In excel, it can be computed using the RATE function with the following syntax…
12*RATE(term,base payment,-adjusted cap,residual,1)
The 1 means payments are made one month in advance.
1 Like
In order to determine the unearned rent, you’ll need to create an amortization schedule. There is a much easier way to determine the adjusted lease balance in excel without worrying about unearned rent charges thus bypassing amort schedules. Below are the two excel functions that you’ll need to compute the adj. lease balance…
Adj Bal. = PV(RATE, pays remaining,-pay,-rv,1)
RATE = RATE(term,pay,-cap,rv,1)
pay = base payment
cap = adj. cap
rv = residual value
RATE = periodic (monthly) rate implicit in the lease
EDIT:
The Excel PV function computes the sum of the present value (pv) of the remaining base payments and the pv of the residual value to arrive at the adjusted lease balance.
1 Like
Does it speak poorly of me that I really want to see your full excel calcs?
I’m a lowly finance student, but this is incredibly interesting - I’ve done the same for loan amortization, but not for leases.
Yup.
As a former math and econ instructor, I wouldn’t be doing you any favors by just handing over the calculations. If you find them incredibly interesting, it seems to me that you would have a strong desire and motivation to derive the equations. Suggest you read the posts on this thread. The RATE function that I provided and the amortization schedule I posted above, should provide you with all the information you need to determine the calculations. A lease amort schedule is exactly like a loan amort schedule except payments are made at the beginning of the month instead of at the end of the month (i.e., lease payments are made one month in advance).
1 Like
The traditional pmt, ipmt, ppmt functions don’t have merit when I tried them, so this is gonna be a weekend project, I’ll take it under advisement!
You may not be using them correctly. Remember, that most fund providers use a money factor to compute the base payment…
FYI- If you have a TI83/84 calculator, you can find the annual interest rate (12 x RATE) implicit in the lease as follows…
N = Term
I% = Calculate this will give you 12 x R ATE
PV = - Adj. Cap
PMT = Base Payment from the MF formula
FV = Residual Value
P/Y = 12 payments per year
C/Y = 12 number of compounding periods per year
PMT Convention: Beginning of Month. Lease payments are made one month in advance
OR
Use the Excel RATE function to compute the annual interest rate implicit in the lease as follows…
12 x RATE(term,payment,- adj cap, residual,1)
If you want to know the outstanding lease balance at any point in time, construct a lease amortization schedule like the one posted above or, you can compute it using Excel’s Present Value (PV) function with the following syntax…
Adj. Lease Balance = PV(RATE, # of payments remaining,- base payment,-residual,1)
Another option is to use the TI83/84 to get the adj. lease balance as follows…
N = # of payments remaining
I% = 12 x RATE use the Excel RATE function to get RATE
PV = Compute this gives the Adj. Lease Balance
PMT = - Base Payment from the MF formula
FV = - Residual Value
P/Y = 12 payments per year
C/Y = 12 number of compounding periods per year
PMT Convention: Beginning of Month. Lease payments are made one month in advance
Or
You can use the formula…
where
t = number of payments made
r = monthly interest rate or simply RATE
There are many other methods that can be used to make these calculations. You may want to investigate to discover them on your own.
Hope this helps.
Using the data in the amortization schedule posted above…
Base payment = PMT(eff_rate/12,term,-cap,res,1) = 357.00
The amount allocated to depreciation (principal) when the 12th payment is made…
Prin(12) = PPMT(eff_rate/12,12,term,-cap,res,1) = 331.74
The amount allocated to the rent charge (interest) when the 12th payment is made…
Int(12) = IPMT(eff_rate/12,12,term,-cap,res,1) = 25.26
You can see these allocations in the amortization schedule posted above. Now they have merit!
Ah, I see what I did wrong I didn’t include residual or -cap in my vars. I’ll adjust and see if it lines up.
1 Like
Referencing the above post…
Remember that eff_rate/12 = RATE(term,pay,-cap,res,1). Also, note that 331.74 + 25.26 = 357.00 … depreciation payment + rent charge payment = base payment … as it should.
1 Like
This topic already came up a while back in this thread:
Why MF instead of Interest Rate? - Ask the Hackrs - FORUM | LEASEHACKR
If you can handle the math part, here’s a good workup of it… Magic Number Analysis – “Money Factor” in Auto Leasing | Math Encounters Blog (mathscinotes.com)
Yeah, I can handle the math. Mathscinnotes’ presentation is awful. He mixes notation. (one variable, Psub F, represents two different values… amount financed and lease payment. A few of his equations are wrong. Equation 3 contains the term PP which makes no sense. Equation 4 is ambiguous and makes no sense. This guy needs to thoroughly review his presentations before publishing them, so he doesn’t look foolish.
The above is actually incorrect in this context. You are describing a finance/capital lease. The 36 month lease as described and shown above is an operating lease which has a constant payment throughout with term with a constant depreciation and rent/finance charge split the entire time.
The depreciation expense recognized by the lessor and therefore the balance at any given point the lessee can purchase the vehicle for would decline by the same depreciation figure every single month.
I work for an auto finance company that manages a 20bln in auto leases primarily made up of operating leases so it is my job to know.
1 Like
6 Likes
Try telling that to Ford Motor Credit, GM Financial, Ally Bank, etc. With all due respect, your statement is inaccurate. Read the post again. The depreciation and rent charge over 36 months does not decline at a linear rate. Look at the interest equation for the kth month. It is strictly a convex (non-linear) function. Constant payment, yes. Constant depreciation and rent charge, no.
The graphic and the formula that follows is only an estimate. It was deliberately simplified (using a trapezoid) in order to help the reader understand the money factor formula wherein is shows a constant monthly depreciation payment and a constant rental payment. However, in reality, that’s not what happens. Look at a lease amortization schedule where the monthly constant yield rate is levied on the previous month’s lease balance so that the rental (interest) charge declines each month and the depreciation increases each month. This is analogous to the principal and interest paid in an installment loan each month.
Again, with all due respect, you don’t know. I was a practicing actuary and math professor for many years and have a strong background in financial mathematics. So, I know a thing or two about this stuff.
EDIT: What’s being described is a consumer retail lease under Federal Regulation M.