A Per Diem Formula for Excel

By Hunter Taylor

i Image courtesy of Microsoft Office online

A per diem is the expenses needed to live per day. Business uses the per diem rate to determine the amount to reimburse or allow its employees for expenses incurred on behalf of the business. The formula for per diem can be calculated in Microsoft Excel.


Per diem means per day. In business, it is the expense per day to live away from home.


Depending upon the distance away from home, the components of a per diem can include food, lodging, incidental and transportation expenses. A per diem is a flat dollar amount for each day.


Per diem = Food (breakfast + lunch + dinner) + Transportation (.55 x number of miles) + Incidentals + Lodging (room cost + taxes)

Transportation and Food Costs

The Internal Revenue Service sets the per-mile transportation costs. In some years, travelers are allowed 55 cents per mile and, in others, it has been 58 cents per mile. Usually, a per diem is applicable when a trip is 50 or more miles away from the home residence. The allowance for food is set, according to the general food costs across a state or counties. For places with a higher cost of living, such as New York, the per diem expenses for food will be higher.

Incidentals and Lodging

It is a good idea to budget for unexpected incidentals and to include this in the formula. Using discounted travel sites helps in planning for lodging costs and provides a final cost for per diem formulas.

Per Diem Reimbursements

The expenses incurred can be reimbursed. Just keep track of all trip expenses and submit those receipts.