Calculate Mortgage Payments and Your ROA with an Excel CalcPlot Chart

An Excel CalcPlot chart can plot measures in three dimensions, not merely two—as shown with examples of mortgage interest calculations and Return on Assets.

4220

When you're refinancing, this chart allows you to use the tip of your finger trade off your monthly payments and your total payments as you consider the term of your loan.An Excel CalcPlot Chart allows you to plot values in three dimensions, not merely two. Here are two examples.

Mortgage Paments

Mortgage interest rates are at near-record lows. And this Excel CalcPlot chart can help you to plan a refinancing strategy.

You can copy and paste it to Excel to see it larger than its current size.

Suppose your existing loan is at 6% and has 20 more years to run…as indicated by the far-right point of the plotted grid. There, you can see that your total payments will be about $172,000 per $100,000 owed. And your monthly payments are about $725 per $100,000 owed.

When you're refinancing, this chart allows you to use the tip of your finger or a pencil point trade off your monthly payments and your total payments as you consider the terms of your loan.

Suppose you can refinance for 2.5%. To lower your payments, follow the 20-year line downwards to 2.5%. There, you learn that your monthly payments will be about $500 and your total payments will be about $127,000—per $100,000 borrowed.

Or you could pay roughly your current amount. With that strategy, the 2.5% line upwards until it’s at your current payment, $725. That amount is near the intersection of the 13-year line. Dropping downward from that line, you see that Total Payments are about $117,000.

Return On Assets

Years ago, I used a CalcPlot chart to calculate the ROA of public companies by their profit margins and turnover at that time.

These CalcPlot charts rely on a band of lines to display Profit Margin, Asset Turnover, and Return On Assets.

As you can see in the first chart, Apple’s ROA in 2011 was at about 23%, a ten-year high. It achieved that level by controlling its Asset Turnover while increasing its Profit Margin significantly.

And in the second chart, you can see that Boeing (BAC) was the only company in the Dow with a negative ROA. (If I were creating these charts today, I would add CalcPlot lines that include Boeing’s performance.)

Coca-Cola had the highest Profit Margin of about 34%, but an Asset Turnover of about 0.5—yielding an ROA of about 15%.

On the other hand, Walmart had the highest turnover in the Dow, at about 23%, but one of the lowest margins, at less than 5%—yielding an ROA of about 10%.

And finally, at about 20%, Microsoft had the highest ROA in the Dow that year. It did so because of a high margin and average turnover.