First a word of caution. My 30/360 calculation may only work on Office 365 Excel. It may, but I don’t have an older version to try it with. Recently a reader was having a problem with an internal rate of return calculation. His investment software was giving him a different internal rate of return than […]
Category: Uncategorized
Live Currency Table, Conversions, Bubble Map + Bitcoin
I don’t know if any of my followers can use a live currency table on the job. I remember back in the 80’s when the firm I worked for purchased an insurance company based in London. I had to rely on and trust outside firms to calculate the currency conversions. In any event, since Excel […]
Branch Analysis
The title says “branch”, but this spreadsheet could be used for departments, ATMs, HR (personnel), or any other monthly comparison. The idea is for the data I have entered is to compare branch productivity (could be loan production, deposits, etc.). One to five branches are compared, given one to twelve consecutive months. The totals and […]
Chart to Emphasize the Rise in Home Prices
So, you work for a small bank, credit union, or a mortage related firm, and the CEO asks you for a chart to show the Board what is going on with home prices. You could use several ways (line charts, bar charts, etc.) to show the big rise this year in home prices, but this […]
Variable Rate Plus Interest Only Amortization
A reader was interested in a variable rate mortgage (which I have already posted), however with a twist. He wanted an interest only option for the first given interest rate. Others might find this helpful. Download: http://pistulka.com/Excel_Shared/VRIO.xlsx
Unstructured Amortized Loan Schedule
A reader asked if I could help with a loan between family members that did not have specific payment requirements. Payments could be made on any date and any amount. The lender could increase or decrease the loan by any amount on any date. There was also no set maturity date for the loan. The […]
Solve for Monthly Payments with Actual/365 and Actual/360 Loans
Calculating the monthly payment on loans with dates can be tricky, unless the loan assumes all months have 30 days and there are 12 months in a year (30/360). In that case you can use the Excel PMT function. It is more difficult however to calculate a monthly payment that will pay off a loan […]
Eight years of Retirement (190 Posts)
Since I have retired, I have been publishing Excel spreadsheets, mostly about finance but also about other areas. Many of the older posts have been improved on by more recent posts but are still out there. Some browser searches for subjects I have posted show up on page one or two, but many are buried deep. […]
Negative Amortization Payoff
I am posting this as an example on how an amortization can be programed to find the final payoff with unusual circumstances. I was asked by a reader for help with determining the final loan payment on a special situation loan. For most of you, your inhouse system would probably handle this. Anyway, using an […]
Watts, Volts, Amps, Ohms Calculator (Excel)
I know that this post has nothing to do with finance, but others might find it helpful. It is important to remember to click the “Clear Inputs” button to start over or to enter new inputs. Enter any two of the four electrical measurements in the yellow cells and the other two will be […]
Simple Break-Even for Solar Installation. (Excel)
This Excel spreadsheet is the result of a reader asking for help creating a simple method of determining the break-even point for a solar power installation. It involves taking out a loan and applying the expected savings on the utility bill against the loan payments. The critical variables are: How much will initially be saved […]
Present Value of a Future Annuity
Note: I found an error in the spreadsheet calculations, so if you saved a copy before 3/10/2021, download it again. These calculations could come in handy in various scenarios. Say the firm you work for has a pension plan that they want to close out and pay off participants early. What would those payouts in […]
Percentage Change in GDP (Scroll Bar)
I have posted GDP charts in the past, but this one uses a scroll bar to view the percentage change for each quarter since 1947 to the present. The chart includes recessions and an adjustable moving average. Also, the declines are in red. Download: http://pistulka.com/Excel_Shared/fred_gdp.xlsx
Chart Organization’s Salaries / Bonuses
These charts are geared to small banks or credit unions that want to have an informative visual of department salaries and/or bonus. It can be used on larger institutions as well, but I suppose the larger financial institutions have access to HR software that can provide graphics. I must give credit for the graph I […]
Compliance Risk
In February of 2017 I posted an Excel spreadsheet called “Internal Audit – Interactive Report”. With the newer functions in Excel, I thought I might try a different approach. I used nine boxes to graphically show which areas in the bank or credit union have the most risk to the organization. First, I listed various […]