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 either Excels IRR or XIRR was giving him. The difference was small, but he needed to be able to confirm the rates of return. The products his system was analyzing were bonds. The same could hold true with mortgages as well. It turned out his software was using a 30/360 basis to calculate internal rate of return.

If you are familiar my mortgage amortization schedules, you might have noticed that I us IRR to calculate returns on these schedules. It is seldom a problem, if all periods between cashflows are the same, including the first payment. A problem shows up however, when the time between the settlement of the loan and the first payment differs from the other time periods, which are the same.

You might think that this problem can be overcome by using XIRR, which uses dates that are not necessarily spaced the same time apart. It would, except for the fact that bonds and mortgages are typically based on 30-day months and a 360-day year (30/360) basis. XIRR uses the actual time between each cash flow and assumes a 365-day year (actual/365) basis.

Internal rate of return (XIRR) is complex formulas (as is IRR), in that it is not solved directly. XIRR represent the sum of the present values of each cash flow, based on the time each cash flow is outstanding. Through a process of iterations, various rates of return are applied to the cashflows. The rates are narrowed down until the sum of the presents values of each cash flow is equal to the original investment (or in other words the sum of the present values minis the original investment = zero) that rate is the internal rate of return.

Back to my readers problem. I had written a VBA program that would calculate the internal rate of return on a 30/360 basis, and that solved the problem.

It got me thinking, however, that I could use Goal Seek to solve for internal rate of return on a 30/360 basis. The first step is to create a formula that will calculate present value using a 30/360 basis. It turns out it is not that difficult. Below are four ways to calculate present values:

For these examples I used “MyDates” as the list of dates that correspond to the cashflows, and “MyData” as the list of cashflows. R represents the interest rate used to calculate the present values. “C” represents the first cash flow date (used only in the 30/360 formula).

- Excel function (Actual/365) =XNPV(R,MyData,MyDates)
- Formula (Actual/365) =SUM(MyData/((1+R)^((MyDates-INDEX(MyDates,1))/365)))
- Formula (Actual/360) =SUM(MyData/((1+R)^((MyDates-INDEX(MyDates,1))/360)))
- Formula (30/360) =SUM(MyData/((1+R)^((DAYS360(C,MyDates,FALSE))/360)))

With formula four, I now had a way to calculate XIRR on a 30/360 basis. All I had to do was use Goal Seek to substitute various rates into formula four, until difference between the original rate for XIRR minis a new rate equaled zero. That new rate will be the 30/360 XIRR rate.

Note that I used a small VBA formula to run Goal Seek, whenever the “Solve” button is pushed.

Now we can take the 30/360 day solved rate which is an annual rate and convert that to a monthly rate and both the 30/360 XIRR rate and the IRR monthly rate (times 12) are both 5.000%.

You may never have the same frustration as the reader I mentions at the beginning to match returns, even if they are small. Maybe though it can give you a better understanding between IRR and XIRR.

Download: http://pistulka.com/Excel_Shared/XIRR_Using_Goal_Seek.xlsm

]]>

In any event, since Excel 365 offers live currencies, I thought I might demonstrate how you can construct a table and an uncomplicated way to convert currencies.

For the sake of time, I only have eight currencies in the table, but it can be added to. You will have to unhide columns D to L to see how the currency prices are downloaded.

Conversions:

You might not have realized that Excel includes the live price of Bitcoin in various currencies. I included the value of Bitcoin vs the USD.

I also have a bubble chart with other currencies vs the USD, on a background of a world map. Not much value, except to recent graduates that don’t seem to have been offered geography classes. Sorry, sometimes I can’t help myself.

The reason the Excel workbook is in the .xlsm format is that the “Refresh Data” button requires a short macro.

Download: http://pistulka.com/Excel_Shared/CurrencyTable.xlsm

]]>

One to five branches are compared, given one to twelve consecutive months. The totals and averages for the branches and months selected are shown. Up to one hundred branches can be entered.

Conditional formatting will show the branches and months chosen. I copied the table into five more sheets and labeled them, just to show some of the uses. The light-yellow cells are the input cells for analysis. Each yellow cell is a drop-down box. If you want to compare less than five branches, choose the first option in the yellow branch drop-down box, which is “Branch 0”. If you want only one month to compare, make both yellow monthly drop-down boxes the same month.

]]>

I used the median price for California homes, provided by the California Association of Realtors. You would obviously use prices of interest to your firm.

One advantage to this type of chart is that you don’t need seasonal monthly adjustments. For example, with this data it is obvious that after August (due to school starting), prices typically take a dip or flatten out.

Also, it shows that home prices in 2020 dipped below both 2019 and 2018 levels in May.

The chart shows the dramatic breakout in prices starting in the second half of 2020 and continuing into 2021.

To chart data, which typically comes in two columns (dates and prices), it is easier to chart months (text) in one column and the other columns of dates and prices:

Offset formulas are in the blue cells to transfer the data in columns to data that is easier to chart. Each blue cell (above) is the same except the 12 in the first formula is replaced with 24 in the second formula and so on in multiples of 12. It is more obvious when you see the spreadsheet.

Formatting the chart looks like this:

Download:http://pistulka.com/Excel_Shared/RE_Prices.xlsx

]]>

]]>

A normal amortization would have specific coupon payments expected on specific dates, with a payment that was calculated to amortize the loan to zero after a specific time. You can see how using a normal amortization would be too unwieldy.

After a few ideas, my final shot on such an unstructured loan can be downloaded below.

There naturally are certain basic rules that must be adhered to make the schedule work:

You must remember to enter the compounding dates. Your options are Monthly, Quarterly, Semiannually, or Annually.

I have not tested this amortization in the real world, so as usual, use at your own risk.

]]>It is more difficult however to calculate a monthly payment that will pay off a loan with the same payment each month if the loan uses the actual days in each month to calculate interest (Actual/365 or Actual 360). Simple interest loans are good example of loans that require actual days in each month. The problem is not only do months not have the same number of days, but that February has an extra day every four years. Therefore, when using the PMT function, the last payment will be larger than the rest, or an extra payment may be needed.

To overcome this problem, I have incorporated Excel’s Goal Seek into the calculation.

Once the input data above is entered in the spreadsheet, an amortization schedule is produced. The last input is a dropdown with three options (30/360, Actual/365, and Actual/360). When you change the last input (Interest Accrual) a new payment is calculated to amortize the loan to zero at maturity,

Here is how it works:

With the xlookup function it finds the last payment in the amortization schedule. Then using the same xlookup function it finds the next to last payment. Then it subtracts the two for the difference (cell j6). The payment is in cell E15. Then when either the “Solve for Payment” button is pushed or the Interest Accrual cell is changed, Goal Seek triggers and changes the payment in cell E15 until the cell containing the difference between the last and next to last payments equals zero.

If the only input you change is the “Interest Accrual”, calculating the appropriate payment is automatic. Changing any of the other of the four inputs will require clicking the “Solve for Payment” button.

]]>

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 amortization schedule with an extra payment option can help for this type of situation.

The problem looked something like this:

A borrower takes out a $200,000 loan at 5.00% for 30 years, with a 5-year balloon. The monthly payment was $1,073.64. The penalty, if the loan is not refinanced or paid off at the end of 5 years, is a new rate of 15%.

The new payment at 15% would be $2,352.34, calculated using the remaining term of the loan (300 months).

Instead, the borrower continues to make the original payment ($1,073.64) on the loan. This obviously results in hefty negative amortization starting at payment 61, since the difference between the old payment and the new payment is $1,278.70.

The borrower continues to make the original payment through payment 80. Then on payment 81, starts making the payment he should have been making all along of $2,352.34. This is where the Extra Payment column comes in handy. Instead of making the payment change at payment 81, we just add on the difference between the two payments ($1,278.70) as an extra payment each month.

Finally, at payment 175, the borrower decides to refinance. What is the final payoff? The principal balance at payment 175 is $262,266.49 and Interest is $3,278.33 for a total of $265,544.82.

Although I doubt a situation just like this will happen very often, it shows that amortization schedules can be programed to solved for negative amortization.

Download:

http://pistulka.com/Excel_Shared/Negative_Am.xlsx

]]>

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 calculated (in red).

O = Resistance in Ohms

A = Current in Amps

V = Voltage in Volts

W = Power in Watts

The calculated units can be converted to:

Micro

Milli

No Change

Kilo

Mega

The abbreviations for these sizes will appear to the left of the changes.

The calculations are in hidden columns. Unhide between columns N and P. The formulas are on the “Formulas” sheet.

]]>The critical variables are:

- How much will initially be saved on the electric utility bill.
- How much will the electric utility bill increase on average each year going forward.

At what point in the future will the cumulative savings on the electric bill have paid for the cumulative cost of the loan, and how much will the cumulative savings be after that.

I imagine the initial savings from the installer can be fairly accurate, however the average annual increase in the electric bill in the future may be more problematic. Even if you have historical evidence of past utility bill increases, applying an assumed increase for the next ten or more years may be risky. There are potentially new discoveries and methods of cheaper power generation to consider, plus better ways to store electricity, such as improvements in battery storage.

The only caveat for entering inputs in this spreadsheet is that Tax Rebates (if any) must be entered as a negative number, and only yellow cells are input cells.

Download: Solar

]]>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 the future be worth now? Or you own an annuity that will pay you a certain amount each quarter for life, starting in five years, but you have the option to sell the annuity now. In both cases, what you need is to calculate the present value of getting the proceeds of future payments, in the form of a lump sum payment.

The above is an example of a 60-year-old that expects to receive $3,500 a month from a pension for life starting in 5 years, when he is sixty-five. To calculate the current value, we need to make some assumptions. First, assume the life expectancy for this person is seventy-seven. So, we use 144 months which is 12 years from retirement. Now we need an interest rate to discount those future payment for the 12 years. In this case we assume 5.00%, compounded monthly. The payment type is not that important at this point, so we assume “End of Period”. That calculation yields a present value 5 years from now of $378,423.

But how much is it worth today? We need a second present value calculation on the $378,423, using 5.00%, compounded annually and get a current value of $296,504.

This simple calculation does not take into consideration taxes for taking a lump sum, nor what tax rates will be like in the future.

Download: http://pistulka.com/Excel_Shared/Forward_Settlement.xlsx

]]>This example institution has ten departments. Each dot represents the salary/bonus of an employee, his/her department, and pay level or bonus level.

The data under the chart is not part of the chart and must be aligned with the chart.

Cell B4 on the charts is a single cell that is used to align the paygrades or bonus levels to the minor ticks. It is linked to each cell on row twenty-eight.

I included a third chart and instructions on how to add more departments and employees.

The employee numbers are just to keep track of all employees and are not used in the charts.

Download: http://pistulka.com/Excel_Shared/Salary_Chart.xlsx

]]>

For example, the General Ledger might have a substantial risk if errors were made, but the likelihood of a major problem was small. Therefore, I would give the General Ledger a 3 for risk and a 1 for likelihood of a problem. The idea is to create a form that divides up the risk / likelihood as below:

All the areas of the organization are compiled and rated into a table (called T) in the sheet called “Data”. Then in the “Box” sheet I used the Filter function to divide up the groups into their appropriate box. The filter function looks like:

FILTER(T[Departments],T[Both]=F5,”NONE”)

This spilled each area of the organizations into its appropriate box. If you have the responsibility of compliance in your firm, this sheet could be used to show management, or the board, which areas need the most time and effort to evaluate.

Download: Compliance_Risk

]]>