Equipment loan payments depend on the amount financed, interest rate and payment count; divide the monthly payment by billable hours for its hourly share.
Key takeaways
- Copy the amount financed, interest rate and payment count from the signed agreement.
- Match the rate period to the payment period and check the result against the lender's schedule.
- Divide the payment by realistic billable hours and keep financing separate from other equipment costs.
Why the payment belongs in your hourly rate
If your shop carries a financed machine, the loan payment arrives every month whether the machine works that month or sits in the yard. An hourly equipment rate is the way you recover that cost, one billed hour at a time. Set the rate too low and the payment quietly comes out of your margin. Set it too high and you may lose bids to a competitor who priced the same work more carefully.
Before you can spread a payment across hours, you need the payment itself. For a standard installment loan, the monthly payment depends on three inputs: how much you borrow, the interest rate, and how many payments you make. If any one of them is wrong, the monthly figure is wrong, and every hourly number built on it is wrong too.
This post walks through each of the three inputs, explains where each number should come from, and shows how to turn the monthly result into a rate you can put on an estimate. The examples use the same payment function that spreadsheet programs provide, so you can check your work without doing the algebra by hand.
Input 1: the amount you finance
The first input is the principal, the amount you borrow. Microsoft's reference for the spreadsheet payment function describes the present value of a loan as the total amount that a series of future payments is worth now, and notes that this is "also known as the principal" ( Microsoft, WorksheetFunction.Pmt method). In practice, that is the amount the lender actually funds, not the sticker price on the machine.
For most equipment purchases, the principal is the purchase price minus your down payment and any trade-in credit applied to the price. Some buyers also roll sales tax, delivery charges, or documentation fees into the loan. If those costs are added to the balance, they belong in the principal, because you pay interest on them along with everything else. If you paid them in cash at signing, leave them out of the principal and count them as a separate cost.
Use the financed figure from the signed agreement, not the number on the original quote. Quotes often show a price before tax and fees, and the payment schedule reflects what was actually financed. A small difference in the principal changes the monthly payment, and that change carries straight into the hourly rate you bill. Pull the number from the document, write down where you found it, and date the note so you can trace it later.
One common trap is a trade-in. If your old machine was applied against the price, the principal drops by that credit. Do not also count the trade-in as income inside the same hourly rate unless you have a clear reason to, because that double-counts the benefit. Keep the loan math about the loan. Handle the trade-in on a separate line of your cost sheet so the two stories stay straight.
Input 2: the interest rate, converted to a monthly rate
The second input is the interest rate, and this is where most rate calculations go wrong. Loan agreements usually state an annual rate, but your payments come due monthly. The rate you feed into the calculation has to describe the same period as the payment. A monthly payment needs a monthly rate.
Microsoft's example shows the conversion. For a four-year loan with monthly payments at an annual interest rate of 12 percent, the documentation says to "use 12%/12 for rate and 4*12 for nper" ( Microsoft, WorksheetFunction.Pmt method). Two things happen in that example. The annual rate is divided by 12 to get the monthly rate, and the term in years is multiplied by 12 to get the number of months. Both changes keep the rate and the count describing the same unit of time.
The same page shows the opposite case. If you make annual payments on the same loan, the documentation says to "use 12 percent for rate and 4 for nper." The rate stays annual and the count stays in years. A monthly rate paired with a yearly count, or an annual rate paired with a monthly count, produces a payment that looks plausible and is wrong.
Some practical habits help:
- Write the annual rate in its own cell, labeled as annual, so nobody mistakes it for the monthly figure.
- Put the monthly rate in a second cell with the formula visible, such as the annual cell divided by 12.
- Use the rate from your signed agreement, not a rate from an advertisement or a verbal estimate.
- If the agreement lists a rate alongside separate fees, confirm which figure the payment schedule is built on before you copy it into your model.
Keep an eye on rounding, too. A rate shown to two decimals in a conversation may not match the exact figure in the contract. Use the contract figure in the formula. If your result and the lender's payment schedule disagree, the schedule is the better check on what you will actually pay, so reconcile to it.
Input 3: the number of monthly payments
The third input is the term, meaning the total number of payments. Microsoft's reference defines it in exactly those terms: "Nper - the total number of payments for the loan" ( Microsoft, WorksheetFunction.Pmt method). For a monthly loan, that count is the number of months, not the number of years on the note.
This is the input where a single slip has the biggest effect on the output. Estimators often think in years because that is how a loan is described in conversation: a four-year note, a five-year note. The formula, however, wants the count of payments. Writing the multiplication into the cell, rather than typing the final number, makes the conversion visible to whoever reviews your sheet later.
The term also shapes the trade-off you are weighing. A longer term spreads the principal over more payments, so each payment is smaller and the hourly share of the payment is smaller too. But the loan runs longer, so you pay more interest over its life, and the machine has to keep earning for longer to cover that. A shorter term does the reverse: a larger monthly payment, a larger hourly share, and less total interest. When you compare two financing offers, do not compare only the monthly figure. Put both offers through the same calculation at their own terms, then compare the monthly payment and the total amount paid side by side.
Be careful about the schedule's final payment. If the agreement includes an unusual final payment, or if the regular payments do not fully pay the balance over the stated term, the simple count of months will not match what you owe. Read the schedule before you trust the count. If the lender's schedule and your term do not agree, rebuild the model from the schedule.
Two inputs you can usually leave alone
The payment function has two more inputs. You do not need them for a standard installment loan, but it helps to know what they do so you can set them correctly when a contract is unusual.
The first is the future value. Microsoft's documentation says: "If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0." For an ordinary loan that is paid off by the end of its term, a future value of zero is the right setting, and you can leave the input out of your formula.
The second is type, which tells the function when each payment falls due. The documentation describes it this way: "Type - the number 0 (zero) or 1; indicates when payments are due." Match this input to the payment timing in your agreement. Read the payment terms in the contract and match the setting to them. If the contract is silent, ask the lender rather than guessing, because a guessed timing setting can shift the result in a way that is hard to spot by eye.
Keep the two optional inputs in view even when you leave them alone. Write a note next to the formula saying which settings you used and why. That note saves time the next time someone asks how the number was built.
Putting the three inputs into a formula
The spreadsheet payment function takes these inputs and returns the payment. Microsoft describes it as a function that "Calculates the payment for a loan based on constant payments and a constant interest rate." That fits most equipment loans, which are normally a fixed payment at a fixed rate. If your contract has a variable rate, the function will not capture the changes, and you will need to re-run the calculation each time the rate moves.
A simple layout keeps the model readable. Give the principal, annual rate and term separate labeled cells.
A1 Amount financed (principal)
A2 Annual interest rate (enter as a percent)
A3 Term in years
A4 Payments each year 12
A5 Monthly rate =A2/A4
A6 Number of payments =A3*A4
A7 Monthly payment =PMT(A5, A6, A1)
Setting up the annual payment-count cell makes the monthly assumption explicit, and it is easy to change if your schedule is different. The formulas divide the annual rate by the annual payment count and multiply the term by that count.
If the result shows an unexpected sign, check how you entered the amount financed before you carry the figure into the hourly rate. You want the size of the payment, not a sign artifact. Then test the sheet by changing one input at a time. A higher principal should raise the payment. A higher rate should raise it. A longer term should lower it. If a change moves the result the wrong way, a reference is probably pointing at the wrong cell.
From monthly payment to hourly rate
Once you have a monthly payment, the conversion to an hourly figure is a single division. Divide the monthly payment by the number of hours you expect the machine to bill in a month. The result is the financing share of your hourly equipment rate. Every other cost in the rate, such as fuel, maintenance, insurance, transport, and operator time, needs its own calculation and goes into the rate beside this figure. For the fuel component, see building a fuel line.
The hard part is the hours figure. Most mistakes here are optimistic. An estimator who assumes the machine will run every working day for the full shift will set a financing share the business cannot reach. Use hours you can defend: billable hours from your records for similar machines, adjusted for seasonality, weather, and the time the machine spends in transport or waiting on a crew. Write the assumption down next to the number.
Run the division both ways to see how sensitive the result is. Calculate the financing share at your expected hours, then at a lower figure. The gap shows how much a slow month pushes the hourly cost up. If the gap is large, decide whether the rate should carry a larger financing share at a conservative hours level, or whether the machine's utilization needs a plan before you commit to the loan.
Keep the financing share separate in your estimate sheet, so the number is visible when someone asks what the rate is made of. A rate that is one lump sum hides its assumptions, and hidden assumptions are the first thing to break when a job goes sideways. The monthly payment covers the loan itself. Whether the rate should also set aside money for eventually replacing the machine is a separate decision, and it is outside the scope of this post.
Illustrative example: spreading the payment across hours
For an illustrative monthly payment of 1000 and 50 billable hours, the financing share is 20 an hour. At 20 billable hours, the same payment requires 50 an hour. These illustrative figures show the financing share only; fuel, maintenance and other equipment costs still need their own calculations.
Check the numbers before every estimate
Set a routine. Before you issue an estimate for a financed machine, open the sheet and confirm three things. The amount financed matches the signed agreement. The annual rate matches the agreement and has been converted to a monthly rate. The term has been converted into months. Then confirm that the monthly payment you calculated matches the first payment on the lender's schedule, allowing for the rounding the schedule uses. If it does not match, stop and reconcile before you bill anything.
Review the sheet whenever something changes: a refinance, an early payoff, a change in the hours you expect, or a second machine added to the same loan. Keep the source document and the date next to each input, so the chain from contract to hourly rate is easy to follow. When a customer or a partner asks how a rate was built, you should be able to answer in a minute from the sheet alone. For the wider estimate, use the construction estimate audit.
When the payment does not fit the rate
Sometimes the math works and the answer is uncomfortable. The financing share is higher than the market will bear on the jobs you bid. Before you raise your rates, review the loan structure and the hours you expect to bill.
The principal is the first lever. A larger down payment reduces the amount financed, which reduces the payment and the share of the hourly rate it drives. Whether that is practical depends on your cash position, so weigh it against the cost of tying up cash that could cover other needs.
Model a longer term alongside the current term before you choose it.
If you can raise utilization by booking the machine on more jobs, the same payment spreads over more billed hours. That is a sales and scheduling decision more than a finance decision.
A fourth option is to show the financing piece separately from the rest of the rate in your bids. Some estimators present the equipment rate as one combined figure, which hides where the cost comes from. Showing components can help you explain why a rate is set where it is, and it helps you spot when one component has drifted away from its assumptions.
Finally, if a loan's payment makes the machine's rate uncompetitive for the work you actually do, the machine may be the wrong one for that work, or the loan may be the wrong structure. Those are business decisions. This calculation should inform them, not make them for you.
Your next step today
Open the signed loan agreement and payment schedule beside your spreadsheet today. Trace each input to its document, reconcile the calculated payment, and record the billable-hours assumption before using the financing share in an estimate.
Questions
Does the loan payment cover every equipment cost?
No, the payment covers the loan itself. Calculate fuel, maintenance, insurance, transport and operator time separately.
What should I do if the spreadsheet payment differs from the lender's schedule?
Reconcile the difference before using the result in an estimate. Check the amount financed, rate, payment count, timing and rounding against the agreement and schedule.