Direct Financing Lease Interest Calculation

kthompson1

New Member
Joined
Sep 15, 2017
Messages
2
Hello, I'm using a calculation example from accounting guidance related to direct financing leases. There is a monthly rent payment that is fixed for 9 years (10,000 per year) and an initial net investment amount of $65,100. At the end of the 9 years, the lease will have a residual value of $5,260. I can use a simple formula to calculate the net investment at the end of each year by taking the previous net investment amount less the difference between the monthly rent payment and the monthly interest income. However, I need to somehow calculate the monthly interest income to a) be a consistent percentage of the previous years' net investment and b) result in $5,260 net investment at the end of the 9 year lease. The example result is 8%, essentially an amortization rate. As a result A=5,208; B=4,825; C=4,411; D=3,964; E=3,481; F=2,959; G=2,395; H=1,787; I-1,130.

The issue is I need the formula to then calculate the monthly interest income on an actual lease I'm entering into.

[TABLE="width: 655"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Monthly Rent Payment[/TD]
[TD]Monthly Interest Income[/TD]
[TD]Net Investment at end of period[/TD]
[/TR]
[TR]
[TD]Beg.[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$65,100[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10,000[/TD]
[TD]A[/TD]
[TD]AA=651000-(10000-A)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10,000[/TD]
[TD]B[/TD]
[TD]BB=AA-(10000-B)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10,000[/TD]
[TD]C[/TD]
[TD]CC=BB-(10000-C)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10,000[/TD]
[TD]D[/TD]
[TD]DD=CC-(10000-D)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10,000[/TD]
[TD]E[/TD]
[TD]EE=DD-(10000-E)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10,000[/TD]
[TD]F[/TD]
[TD]FF=EE-(10000-F)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]10,000[/TD]
[TD]G[/TD]
[TD]GG=FF-(10000-G)[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]10,000[/TD]
[TD]H[/TD]
[TD]HH=GG-(10000-H)[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]10,000[/TD]
[TD]I[/TD]
[TD]5,260.00
[/TD]
[/TR]
</tbody>[/TABLE]


1 The rate for amortizing the unearned income and initial direct costs to produce a constant periodic rate of return on the
remaining net investment is X%. This can only be determined by trial and error or by using a computer program.
2 This is the unguaranteed residual value at the end of the lease term.

Thank you for any help!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
[TABLE="width: 1109"]
<colgroup><col><col><col span="2"><col><col span="3"></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]Monthly Rent Payment[/TD]
[TD]Monthly Interest Income[/TD]
[TD][/TD]
[TD]Net Investment at end of period[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD][/TD]
[TD="align: right"]65100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]58450[/TD]
[TD]AA=651000-(10000-A)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]51800[/TD]
[TD]BB=AA-(10000-B)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]45150[/TD]
[TD]CC=BB-(10000-C)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]38500[/TD]
[TD]DD=CC-(10000-D)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]31850[/TD]
[TD]EE=DD-(10000-E)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]25200[/TD]
[TD]FF=EE-(10000-F)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]18550[/TD]
[TD]GG=FF-(10000-G)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]11900[/TD]
[TD]HH=GG-(10000-H)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]5250[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5260[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]you say 10000 per month, this is labelled year 1 to year 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]can you say if the above is anywhere near to your needs ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 1109"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Monthly Rent Payment[/TD]
[TD]Monthly Interest Income[/TD]
[TD][/TD]
[TD]Net Investment at end of period[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD][/TD]
[TD="align: right"]65100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]58450[/TD]
[TD]AA=651000-(10000-A)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]51800[/TD]
[TD]BB=AA-(10000-B)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]45150[/TD]
[TD]CC=BB-(10000-C)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]38500[/TD]
[TD]DD=CC-(10000-D)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]31850[/TD]
[TD]EE=DD-(10000-E)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]25200[/TD]
[TD]FF=EE-(10000-F)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]18550[/TD]
[TD]GG=FF-(10000-G)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]11900[/TD]
[TD]HH=GG-(10000-H)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]3350[/TD]
[TD="align: right"]5250[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5260[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]you say 10000 per month, this is labelled year 1 to year 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]can you say if the above is anywhere near to your needs ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

That is very close to the solution, within $10. However, the monthly interest income must be the same percentage of the previous years' net investment, which I must not have previously mentioned so that's my fault. So in the solution provided in the guidance, Year 2's interest income is $4,825 (B), which is 8% of year 1's net investment of $60,308. The 8% is unknown initially and is what I'm having trouble determining how the example concluded at 8% in order for me to apply the math/formula to a real-life lease.

Thank you!
 
Upvote 0
[TABLE="width: 939"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]However, I need to somehow calculate the monthly interest income to a) be a consistent percentage of the previous years' net investment and b) result in $5,260 net investment at the end of the 9 year lease. The example result is 8%, essentially an amortization rate. As a result A=5,208; B=4,825; C=4,411; D=3,964; E=3,481; F=2,959; G=2,395; H=1,787; I-1,130.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]The issue is I need the formula to then calculate the monthly interest income on an actual lease I'm entering into.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]Monthly Rent Payment[/TD]
[TD]Monthly Interest Income[/TD]
[TD]Net Investment at end of period[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beg.[/TD]
[TD]$ -[/TD]
[TD]$ -[/TD]
[TD]$65,100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10,000[/TD]
[TD]X[/TD]
[TD]55100+x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10,000[/TD]
[TD]Y[/TD]
[TD]45100+x+y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]10,000[/TD]
[TD]Z[/TD]
[TD]35100+X+Y+Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]10,000[/TD]
[TD]D[/TD]
[TD]25100+X+Y+Z+D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]10,000[/TD]
[TD]E[/TD]
[TD]15100+X+Y+Z+D+E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]10,000[/TD]
[TD]F[/TD]
[TD]5100+X+Y=Z+D+E+F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]10,000[/TD]
[TD]G[/TD]
[TD]-4900+X+Y+Z+D+E+F+G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]10,000[/TD]
[TD]H[/TD]
[TD]-14900+X+Y+Z+D+E+F+G+H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]10,000[/TD]
[TD]I[/TD]
[TD]-24900+X+Y+Z+D+E+F+G+H+I[/TD]
[TD]< < 5260[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]2 QUESTIONS should it be 55100 - X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]is not monthly interest a fixed % according to market rates ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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