## California Home Prices Dashboard

The California Association of Realtors® (CAR) publishes monthly home price data for each county in California. I produced a dashboard using their data and the 2010 census for income and population data. In order to keep the dashboard up to date, the “All Counties” sheet stores the data from CAR. There is a link to […]

## Asset Growth vs. ROA – Net Worth Ratio

This concept came from a calculator that was readily available on the internet years ago. I would give credit to the original author, but I don’t remember the name. It was made available online by either CUNA or one of the other credit union associations. I have only embellished it with the addition of charts and a […]

## Solve for Last Payment Due

In my first blog post back in August, I had a formula that would solve for future value given dates and amounts. In this post we will use the same formula to solve for the last payment due. There are a number of scenarios where the last payment might be needed. Assume for a moment that you won a court judgment […]

## Bonus Checking

To understand this next spreadsheet, you have to take your accounting hat off and put your financing hat on. Consider all your sources of funding; savings accounts, money market accounts, CDs, borrowing, and checking accounts. There may be more, but considering the above, checking accounts will always be the cheapest source of funds. The reason […]

## Seasonality

Depending on the industry you are in, the seasons will likely play some part in your bottom-line. Back at the credit union, we knew that our deposit flows were affected by the time of year, holidays, and the makeup of our membership, many of whom were teachers. For my example spreadsheet, I used a set […]

## Sum Amounts Sold During Month & Year

You have a table of products, sales dates, and amounts: Objective: Sum all sales amounts , from the same product, during a certain month and year. Input in yellows cells only, any date within the month and year to be summed and a product code: The answer has two formulas that are the same formula except one […]

## Forward Rates Part 1: GAP Analysis

Gap Analysis, sometimes referred to as breakeven analysis, will get us 3/4 of the way to understanding forward rates. There are three different interest rates involved with gap analysis: Term Rate: Security with the longest term to maturity. Head Rate: A shorter maturity alternative to the Term Rate. Tail Rate: A rate that starts at the end of […]

## Lump Sum Calculator

Last week I showed  a cash flow calculator for even cash flows give dates. This next calculator solves for one time (lump sum) cash flows. Excel has built in functions for all of these, but this one is slightly different , in that it uses dates as inputs for time. The advantage is that all […]

## Total the amount for each date, on multiple sheets

I ran into this problem in accounting. A workbook was being generated by the general ledger that created separate sheets for each ATM, with dates and deposits.  There were a total of 19 ATMs. Individual dates for each sheet were in column A and amounts in column C. The dates did not necessarily show up in the same […]