Please help with Lease Amortization Schedule

chpurcell

New Member
Joined
Jan 24, 2016
Messages
30
As you can see in the file below, I have to create a lease amortization schedule for a capital lease. Here are the assumptions:

- 360 month lease
- fluctuating payments ->
- $10,000 for years 1-5
- $16,666.67 for years 5-30
- imputed interest rate is 5%

I used the PV calculator, and came up with a PV of $2,899,439.

However, when I set up that amount in the amortization, there's a balance left over after year 30

I believe it's due to the fluctuating payments, but I cannot figure out a way to get this thing to zero.

Any thoughts?

https://drive.google.com/open?id=0B73uAyBFLQpnclRhV045QjZOb2M
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try:-
AB
1Initial Loan
2£229,285.23=PV(0.05/12,60,-833.3333,-237583.45)
3Loan Remaining after 5 years
4£237,583.45=PV(0.05/12,300,-1388.889167)
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="198" style="width: 149pt; mso-width-source: userset; mso-width-alt: 7054;"> <col width="246" style="width: 184pt; mso-width-source: userset; mso-width-alt: 8732;"> <tbody> </tbody>
 
Upvote 0
Apologies !!
Forgot to divide by years to get monthly repayments.
Hopefully this might be more useful:-
AB
1Initial Loan
2£16,236.80=PV(0.05/12,60,-166.666,-9503.34)
3Loan Remaining after 5 years
4£9,503.24=PV(0.05/12,300,-55.555)
<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;"> <col width="198" style="width: 149pt; mso-width-source: userset; mso-width-alt: 7054;"> <col width="246" style="width: 184pt; mso-width-source: userset; mso-width-alt: 8732;"> <tbody> </tbody>

This is my understanding:-
Second equation first.
Tofind the PV for a lease paying $55.555/mth for 300 mths (25 years) @ 5%
$9503.34 = PV(0.05/12,300,-55.555)
Check with PMT function:- Repaymentsof Lease/loan$9503.34
$55.555=PMT(0.05/12,300,-9503.34)
-------------------------
Place$9503.34 in first PV equation:-
$16236.84=PV(0.05/12,60,-166.666,-9503.34)
Checkwith PMT function:- Repayments of lease $16236.84
$166.66=PMT(0.05/12,60,-16236.84,9503.34)
Regrds Mick
 
Upvote 0
Are you the lessor (the one getting the interest on the loan) or the lessee (the one paying the interest on the loan)?
 
Upvote 0
Are you the lessor (the one getting the interest on the loan) or the lessee (the one paying the interest on the loan)?

Lessee (paying interest)

I'm trying to set up the initial loan amount and amortize it.

I can't figure it out using fluctuating payments (years 1-5 vs years 5-30) because there's still a liability after year 30
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top