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.
3 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.
