Please help with Lease Amortization Schedule

[chpurcell]

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

 

[chpurcell]

bump

 

[MickG]

Try:-
[TABLE="width: 353"]
<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>[TR]
[TD="class: xl65, width: 27, bgcolor: #DAEEF3"] [/TD]
[TD="class: xl65, width: 198, bgcolor: #DAEEF3"]A[/TD]
[TD="class: xl65, width: 246, bgcolor: #DAEEF3"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]1[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"]Initial Loan[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]2[/TD]
[TD="class: xl67, bgcolor: white, align: right"]£229,285.23[/TD]
[TD="class: xl67, bgcolor: white"]=PV(0.05/12,60,-833.3333,-237583.45)[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]3[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"]Loan Remaining after 5 years[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]4[/TD]
[TD="class: xl67, bgcolor: white, align: right"]£237,583.45[/TD]
[TD="class: xl68, bgcolor: white"]=PV(0.05/12,300,-1388.889167)[/TD]
[/TR]
</tbody>[/TABLE]

 

[chpurcell]

Try:-
[TABLE="width: 353"]
<tbody>[TR]
[TD="class: xl65, width: 27, bgcolor: #DAEEF3"][/TD]
[TD="class: xl65, width: 198, bgcolor: #DAEEF3"]A[/TD]
[TD="class: xl65, width: 246, bgcolor: #DAEEF3"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]1[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"]Initial Loan[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]2[/TD]
[TD="class: xl67, bgcolor: white, align: right"]£229,285.23[/TD]
[TD="class: xl67, bgcolor: white"]=PV(0.05/12,60,-833.3333,-237583.45)[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]3[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"]Loan Remaining after 5 years[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]4[/TD]
[TD="class: xl67, bgcolor: white, align: right"]£237,583.45[/TD]
[TD="class: xl68, bgcolor: white"]=PV(0.05/12,300,-1388.889167)[/TD]
[/TR]
</tbody>[/TABLE]

Where are you getting your numbers from?

 

[MickG]

Apologies !!
Forgot to divide by years to get monthly repayments.
Hopefully this might be more useful:-
[TABLE="width: 353"]
<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>[TR]
[TD="class: xl65, width: 27, bgcolor: #DAEEF3"] [/TD]
[TD="class: xl65, width: 198, bgcolor: #DAEEF3"]A[/TD]
[TD="class: xl65, width: 246, bgcolor: #DAEEF3"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]1[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"]Initial Loan[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]2[/TD]
[TD="class: xl67, bgcolor: white, align: right"]£16,236.80[/TD]
[TD="class: xl67, bgcolor: white"]=PV(0.05/12,60,-166.666,-9503.34)[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]3[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"]Loan Remaining after 5 years[/TD]
[TD="class: xl66, bgcolor: #DCE6F1"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DAEEF3, align: right"]4[/TD]
[TD="class: xl67, bgcolor: white, align: right"]£9,503.24[/TD]
[TD="class: xl67, bgcolor: white"]=PV(0.05/12,300,-55.555)[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[chpurcell]

The initial loam amount should be 2,899,439?

 

[xenou]

Are you the lessor (the one getting the interest on the loan) or the lessee (the one paying the interest on the loan)?

 

[chpurcell]

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

 

[Tetra201]

Is this what you need?

=-PV(5%/12,5*12,10000,-PV(5%/12,25*12,16666.67,0))

 

[MickG]

https://app.box.com/s/as9e52hvvlu0s5n6doa9mhgkp71m00y4