How to calculate Cash on Cash return in Excel? (2024)

Accounting is loved by many and hated by others. Although the group of those who hate it may be considerably larger than those who love it, there is one irrefutable truth: It is indispensable for any company or business.

One of the most important aspects of accounting is performing cash versus cash calculations. It’s a rather simple task, but calculating it in Excel can be slightly more complicated.

How do you calculate cash on cash?

Although it may sound complicated initially, it is a rather simple formula where dividing the annual net cash flow by the invested capital will be necessary.

But, to better understand the meaning of this formula, it is essential to know the two concepts just mentioned.

Invested capital

Invested capital is all the investment in the form of money made by a certain company to start certain operations and to be able to maintain them in the short and long term.

You must add the initial outlay used to carry out the operation, the closing costs, and all improvements made before the lease of the premises and, assuming they were made for the same purpose, to calculate the capital invested.

Annual net cash flow

Net cash flow is the cash inflow and outflow of a given business. It differs from net income because, in this cash flow, payments already made are considered and not those yet to be completed.

You can measure this element in any period. For this formula, an annual net cash flow measurement is required. In other words, you can calculate the cash flow over an entire year.

Calculating this cash flow can be a bit more complicated. Adding the scheduled rent over the entire year and other income earmarked for the site is necessary.

From this sum, you must subtract the actual vacancy, the expenses allocated for the entire project and operation, such as insurance, maintenance, taxes, etc., and the annual debt service.

What is the cash on cash formula?

As stated above, it is a fairly simple formula and is expressed as follows: Cash on cash= Annual net cash flow/invested capital. Knowing the two values mentioned above is enough, and dividing them by each other to obtain the result.

We want the result in the form of a percentage. Therefore, the number obtained from this division must be multiplied by 100. The final formula looks like this: Cash on cash= (Annual net cash flow/invested capital) ×100.

What is the purpose of calculating cash on cash?

This calculation is usually used in the real estate business. It is intended for properties rented or where you plan to start renting.

It is not one of the preferred formulas to obtain an exact value when the property has been rented for more than a year. Still, it is excellent and useful when you want to evaluate the possibility of renting a certain property.

Large investors often use it to determine whether it is worthwhile to purchase the property, make all the related expenses and rent it out. It is a relatively quick way to yield a medium- or long-term return.

With this formula, investors either become interested in or completely discard a property. As it is a quick calculation, you can apply it to several properties in a short time to choose the most suitable ones.

Can cash on cash be calculated in Excel?

Due to the frequency with which this formula is calculated for various elements, it is ideal to have a template in Excel where only the values need to be modified.

And although you can find many templates of this type on the internet, there are pages where you can download the cash on cash templates in Excel safely. All you need to do is enter your name and email address.

When you have downloaded the template, you must open it and modify the values according to what you have in mind for each property where you want to make this calculation.

Suppose you like to have an impeccable organization. In that case, you can save the calculation obtained for each property with a different name and start the calculation again from scratch for the next.