#### chpurcell

##### New Member
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?

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

bump

#### MickG

##### MrExcel MVP
Try:-
 A B 1 Initial Loan 2 £229,285.23 =PV(0.05/12,60,-833.3333,-237583.45) 3 Loan 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>

#### chpurcell

##### New Member
Try:-
 A B 1 Initial Loan 2 £229,285.23 =PV(0.05/12,60,-833.3333,-237583.45) 3 Loan Remaining after 5 years 4 £237,583.45 =PV(0.05/12,300,-1388.889167)

<tbody>
</tbody>

Where are you getting your numbers from?

#### MickG

##### MrExcel MVP
Apologies !!
Forgot to divide by years to get monthly repayments.
Hopefully this might be more useful:-
 A B 1 Initial Loan 2 £16,236.80 =PV(0.05/12,60,-166.666,-9503.34) 3 Loan 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

#### chpurcell

##### New Member
The initial loam amount should be 2,899,439?

#### xenou

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

#### chpurcell

##### New Member
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

##### MrExcel MVP
Is this what you need?

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

Replies
0
Views
206
Replies
2
Views
603
Replies
0
Views
751
Replies
8
Views
667
Replies
0
Views
345

1,190,920
Messages
5,983,590
Members
439,852
Latest member
balasat

### 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?

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