Free Microsoft Excel 2013 Quick Reference

Lease Payment Calculation

I'm trying to figure out a lease payment for a client of mine. I am leasing them equipment for 3 years, with a present value of $27,023, a residual value of $1, and a 15% rate of return. That part is simple, but I'm trying to account for the 8% cost of money to me. I'm getting that $27,023 from an investor expecting an 8% return. I am charging my client a 15% return and I make my money on the 7% margin. How can I calculate my client's payment using Microsoft Excel?

Post your answer or comment

comments powered by Disqus
Hi Guys,

Just thought I share my new rental lease payments schedule. I am sure there better way to do this. I have broke it into few steps but it works.



I am just looking for a formula that will allow me to calculate the monthly
repayments for a car lease. Thanks in advance

I am tired of not having good lease/buy calculators available so I am trying to throw one together. The biggest problem I have with the ones out there is they automatically say buying is better. I don't think it's that black & white. As a diligent investor, I would like to add the concept of investing the differential between a lease payment and a loan payment.

See the attached and let me know what I'm neglecting or what else I should be considering. I threw this together this morning and look forward to help/comments.

Hey all, so basically I've run into a little snag here.

I had someone create a Excel Spreadsheet for me with a userform for calculating something for me.

Everything is in completion EXCEPT for the formula's represented by the particular fields in the userfield.

Since I'm not THAT familiar with VBA I was wondering if someone could help input the formula's for me. I have the formula's on hand and exactly where they need to go, I just don't know how to do them via VBA.

Any help would be appreciated and if it helps the thought process at all the calculator is based off of:

Please look below if you can help (the red highlighted portion will be the user input and the blue highlighted portion will be the output after "calculate" is pressed):

[MOD EDIT]...... removed image

**Please note the difference between "Tax Rate (GST/PST)" and "Tax Rate" below, they are both different user inputs, I apologize the person who created the userform made it a bit confusing**

Cost of Equipment (Cash, Loan, Lease)= Equipment Cost (User Input)

Bank Interest (Loan)= (Cost of Equipment * 0.032)

Bank Interest (Cash & Lease)= N/A

Monthly Lease Payment Before Taxes (Cash & Loan)= N/A

Monthly Lease Payment Before Taxes (Lease)= ((Equipment Cost/(Preferred Term *12)) * (1+(Lease Rate Quoted/100)))

Taxes (Cash & Loan)= (Equipment Cost * (Tax Rate (GST/PST)/100))

Taxes (Lease)= (Monthly Lease Payment Before Taxes * (Tax Rate (GST/PST)/100))

Lease Buyout (Cash & Loan)= N/A

Lease Buyout (Lease)= (0.1 * Equipment Cost)

Total Equipment Cost Before Tax Savings and Future Value(Cash, Loan & Lease)= Equipment Cost (User Input) + Taxes + Bank Interest

Depreciation (Cash & Loan)= ((100%-(Lease Amount Buyout Rate))/100)*(Equipment Cost)

Depreciation (Lease)= N/A

Bank Interest (Loan)= (Cost of Equipment * 0.032)

Bank Interest (Cash & Lease)= N/A

Lease Payments Plus Buyout (Cash & Loan)= N/A

Lease Payments Plus Buyout (Lease)= Total Equipment Cost Before Tax Savings and Future Value

Total Deductions (Cash)= N/A

Total Deductions (Loan & Lease)= Depreciation + Bank Interest + Lease Payments Plus Buyout

Deductions x Tax Rate (Cash)= N/A

Deductions x Tax Rate (Loan & Lease)= ((Tax Rate/100) * Total Deductions)

Interest Earned On Capital Over Term (Corporate ROI) (Cash, Loan & Lease)= (Cost of Equipment *0.08)

Total Equipment Cost (Cash, Loan & Lease)= Total Equipment Cost Before Tax Savings and Future Value

Less Corporate Tax Savings (Cash)= N/A

Less Corporate Tax Savings (Loan & Lease)= Deductions x Tax Rate

Less Interest Earned (Cash, Loan & Lease)= Interest Earned on Capital Over Term

Less GST Tax Credit (Cash & Loan)= (Equipment Cost * 0.05)

Less GST Tax Credit (Lease)= (Equipment Cost * (Lease Rate Quoted/100))

Net Cost of Equipment (Cash, Loan & Lease)= (+(Less Interest Earned) + (Total Equipment Cost))- (Less GST Tax Credit)

Cash On Hand At End of Term (Cash)= N/A

Cash On Hand At End of Term (Loan & Lease)= (Cost of Equipment + Interest Earned on Capital over Term) - Bank Interest

[MOD EDIT] - Removed attachment

I tried to make a mortgage calcultor but my numbers do not jive with the
realtors web sites. My formula= (100,000*.06)/12 does not include the
interest so my calculations were too low.

How would I figure out a monthly payment for a $100,000 loan at 6% over 30
years? Is there a special formula?



Hi all, I am noticing a significant difference between how Excel calculates
a payment and how regular financial software calculates it. Take an

Term: 60 months
Amount: $25,266
Rate: 6.29% pa

Payment Function: PMT(6.29/360, 60*30, -25266) returns $441.

My financial software gives me $491. That is $50 difference! Why? The
software I am using is Auto loan calculation software and I know it is
right, because it is used by banks.


I need a spread sheet where I can enter the:

Loan amount
Term in months
Note date
First payment date

and excel calculate:

monthly payment

Is there any templets out there, I can't find one? The simplier the better.


How do we calculate the interest and payments of car loan?
Any help please.

Hey everyone I'm looking for some help. I am trying to setup a calculation that will get me an output of total payment to our customers. I would like to enter the zip code of the region from where the customer is from and then have that pull up the average amount of money for the county in the U.S. (I have completed this part).

Then I would like to enter a misc field for Yes/No answer. If the answer is yes, I want to cancel out step one and then enter the new average from another category of averages. Then have another field to enter a number "1 to 100". I want this number to be multiplied automatically by $5.15. Then this answer to be added onto the averages.

Can anyone give me any help! It would be appreciated!


Not sure if this is the correct section for this kind of query but I'd like some assistance with a calculation that I can't seem to figure out.

Essentially it's for calculating night payments for our employee time-sheets. Our staff have very sporadic shifts and are paid extra for working between the hours of 00:00 and 06:00, basically when employee's enter their start and end times I'd like the spreadsheet to automatically calculate how many hours they have worked between those hours, I imagine it's very simple but I cannot figure out which function to use.

To complicate matters, because staff can work shifts which start on one day and finish the next we work on a 48:00 clock basis so its' not only between the hours of 00:00 - 06:00 where they qualify for night payments but also from 24:00 - 30:00 if that makes sense?

Any assistance in working out this query will be greatly appreciated.

Recently I tried to program an Excel spreadsheet to calculate a mortgage
Payment using the =PMT(Rate/12,Term,Loan Amount)*-1 formula that I have used
on many other spreadsheets. These older spreadsheets all work properly, but
they were all written on another computer and imported into the one I am now
using. The formula “=PMT(10/12, 360,100000)*-1” should give me a payment of
$877.57, but it gives me $83,333.33 instead! Is their a flaw in the newer
Excel versions, or a patch I don’t have? Any help would be appreciated.

Hi. Can someone pls help me? I have been searching for a way to keep track of a loan due to me. The interest has been constant however the borrower has missed payments, made late ones and partial ones. The excel loan analysis workbook/sheet does not properly calculate the missed or late payments. Other than that the format/layout is perfect. Do I need to create a new workbook or can I modify the loan analysis worksheet, and if so HOW??
I am VERY new to Excel and not familar with a whole lot yet.
Any help and patience is very much appreciated.
Thanks for your time!!

Hi All,

I am trying to calculate the expire date in the format of Day/Month/Year of the lease agreement. This lease agreement need to be reviwed on every 10 years and started from year 2001.

I am trying this: =A+(365*10).

However, i have problem with the month of Febuary as sometime it finishes on 28th, and sometime on 29th.

Your advise will be very much appreciated.


Hi to all,

I have recently started using formulas in Excel therefore cannot really crack this problem down. In my worksheet, I have a column (A2:A200) which stores dates starting from Jaunuary 1st 2006 to January 1st 2007. The second column (B2:B200) stores the expense type (i.e: Maintanence, gas, lease payment), and the third column (C3:C200) stores the amount spent on the given date and expense type. What I would like to do is to create a formula which calculates the amount spent on maintanence, gas, lease payment during January, February, etc:

Thanks for all the help I can get

I'm building a real estate payment calculator. I'm using the Payment (=PMT) function in Excel 2003. When I add the information that it asks for in its wizard, I receive the correct numerical figures but with a minus sign in front of it making the result negative, i.e. "-$630.13" instead of "$630.13."

Is this file corrupted or have I put something in incorrectly?

Okay, I am lost. I am working on a spreadsheet that needs to come back with a total value for lease months. So I have on sheet that has 56 leases on it with about 4124 lines. I need to create a formula that calculates from the Lease months for each lease to total for the month. So some leases started before others. For example lease 1 starts 4/2006 - 10/2008 and lease 2 starts from 5/2006-11/2008. I need to create a formula on my new summary page that looks up by the date in each lease and calculates the total payment for that month.
Lease 1

Payment70750-xxxSalesTotalPrincipalDateAmountInterestPrincipalTaxPrincipalBalanceAt Inception35,240.00 4/2006729.00 262.27 412.73 54.00 466.73 34,773.27 5/2006729.00 258.81 416.19 54.00 470.19 34,303.08 6/2006729.00 255.33 419.67 54.00 473.67 33,829.41

Lease 2
28039-001PaymentPayment70750-xxxSalesTotalPrincipal DateAmountInterestPrincipalTaxPrincipalBalanceAt Inception35,240.005/2006729.00262.27412.73 54.00 466.73 34,773.27 6/2006729.00258.81416.19 54.00 470.19 34,303.08 7/2006729.00255.33419.67 54.00 473.67 33,829.41 8/2006729.00251.81423.19 54.00 477.19 33,352.22

Summary sheet for all leases
PaymentPayment70750-xxxSalesTotalPrincipalDateAmountInterestPrincipalTaxPrincipalBalanceAt Inception4/2006This is where I need the formula5/2006.6/2006

alright i know this has been mentioned a few times on the board and is still in the archive...but has anyone found the APR calculator in excel to be slightly off from some of the old DOS APR Calculators....

I am recalculating APR's of a loan. I first determine the monthly payment given the up front fees with the formula:

=PMT(L9/12,Q9,F9+Z9,0,0) where

the result is in column I.

Then to recalc the APR I use=

=RATE(Q9,I9,F9,0,0)*12 where

The difference are relatively small but not from an audit standpoint...any ideas to why these numbers might be slightly off?

Normally simple Vlookup functions work great for me. However, I've run into a problem which I believe might be a limitation of Excel. (That or I just want to believe that its not my fault)

Here is the formula in Cell D288

& Here is the formula in cell D289

The value in B2 is currently "2"

Everything on and before D288 work, and everything on and after D289 does not work. I get a "0" return from the formula, even if the target cell is greater.

Anyone know what my problem might be? Is excel finicky when you are looking for letter and number combinations? I have tried checking the formatting of the table, and everything is consistent. I'm at a loss here.

Anyway, here's the formula side of the sheet:

******** ******************** ************************************************************************>Microsoft Excel - SRO Enterprise Report 2008.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC274D274C275D275C276D276C277D277C278D278C279D279C280D280C281D281C282D282C283D283C284D284C285D285C286D286C287D287C288D288C289D289C290D290C291D291C292D292=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I am trying to calculate the interest rate for a lease that does not have
constant payments. I have to pay 1 and last lease payment in advance and some
loan origination fees.

Loan - 500000
payment -19860
up front fees 19000
any suggestions on how to use formula with variable payments and how to
deduct up from fees


I was wondering if anyone could help me with a minimum payment calculation
on my spreadsheet for my Credit card

I have the following Cells

Credit Limit (L2) £4300

Credit Card Amount Owed (L4) £3300

Minimum Amount (L6) 2%

And then I have a cell (L8) Minimum Payment and it is in this Cell I want it
to say what my minimum payment will be if you could help with this that would
be great

Regards Joel

Amount Owed (L6) £3000


My form calculates a payment after adding all of the fields that affect the
total financed amount. Two of the fields that are added to the total financed
amount calculates the credit life and disability premium respectively. If the
credit life and/or the disability Insurance is not selected they return a
blank field.

As long as both Life and disability are both selected the payment
calculates fine. But if either one is not selected and therefore "blank" then
the payment = "blank".

How can I correct this problem? Thanks.

I was wondering how I can use the PMT function (or similar) to take into
account unpaid/back interest. There is no interest on the interest so I
can't capitilize it. Any suggestions on how to setup a formula so that I can
figure out a monthly payment for a fixed term? Thanks in advance

I was wondering how I can use the PMT function (or similar) to take into
account unpaid/back interest. There is no interest on the interest so I
can't capitilize it. Any suggestions on how to setup a formula so that I can
figure out a monthly payment for a fixed term? Thanks in advance

Hello everyone, I have a problem hopefully someone can help.

I have a Amortization Schedule that I downloaded from Microsoft. It almost
does everything I want it to but one thing, so I added my own calculator
that subtracts dates and sums the amount remaining on a loan without interest.

I would like to modify the form section in a way that I can manipulate the
total cost of the loan.

example: I would like to have the loan amount be the final with interest
included (which is the remaining D12) all of D is my calculator. And the
scheduled payment to be the same as the lease payment (D10).

date lease started l D5 l loan amount l H5 l scheduled payment
l K5 l
todays date l D6 l interest rate l H6 l scheduled no.of
payments l K6 l
term of lease l D7 l loan period/yrs l H7 l actual no. of
payments l K7 l
months elapsed l D8 l payments/year l H8 l total early payments
l K8 l
months remaining l D9 l start date l H9 l total interest
l K9 l
lease payment l D10 l extra payments l H10l

amount remaining l D12 l

I would like H5 and K9 to equal D12, K7 to equal D9 and K5 to equal D10
without screwing up the amortization schedule.

I know this is a biggy, but if someone or anyone could help, I'd be in debted

Thanks in advance,

Paul M

No luck finding an answer? You could always try Google.