Here is a quick post on how to calculate the number of days in a year given it in a standard date format. i.e. returns 365 except for 366 on leap years.

=IF(OR(MOD(YEAR(A85),400)=0,AND(MOD(YEAR(A85),4)=0,MOD(YEAR(A85),100)<>0)),366, 365)

Reply

Searching for how comparison interest rates are calculated, I found an article on Tomorrow Finance, but unfortunately a lot of the numbers provided no insight into how they were calculated. I hope to break it down further and explain how to calculate comparison interest rates with Excel. The Excel formula can be quite tricky and the parameters vague, so I will go through each of the formula as well.

**rate**– the interest rate of the loan annually equal to 4.63% (the current rate most banks are offering as of today)**principle**– the loan amount always equal to $150,000 when calculating a comparison rate.**period**– the period in years always equal to 25 years when calculating a comparison rate.

Firstly, calculate the monthly payments for a loan based on constant principle payments and a constant interest rate. The key here is to convert the interest rate to a monthly interest rate and the number of periods also in months.

payment = PMT(rate/12, period*12, principle)

In this case payments equals -$844.86 per month and includes principle and interest. The figure is negative since it is an outgoing.

total payment = payment * 12 * period

Since the monthly payments are all the same, the total payments is simply the monthly payment multiplied by 12 months by 25 years, this equals -$253,456.66.

total interest = total payment + principle

The total interest is imply the total payment (which is negative) plus the principle, this results in a total interest only of -$103,456.66.

A quicker way to calculate the cumulative interest over the lifetime of a loan is;

total interest = CUMIPMT(rate/12, period*12, principle, 1, period*12, 0)

Next the additional fees over the lifetime of the loan are added, usually these are annual service fees, with nab there is a -$395 annual service fees. The total fee over the life of the loan would be 25 years multiplied by -$395 which equals -$9,875.

total outgoings = total interest + total fees

The total outgoings over the life of the loan would be the total interest and total fees, this would equal -$113,331.66.

outgoings = total outgoings / 25 /12

To get the outgoings per month divide by 300 to get -$877.77 per month.

Finally, the comparison rate can be calculated;

The rate is per month which is multiplied by 12 to get an annual comparison rate, in this case equal to 0.050101482 or 5.01%.

Download the WorkBook how-to-calculate-comparison-interest-rate-in-excel.xlsm.

This tutorial will walk you through plotting a histogram with Excel and then overlaying normal distribution bell-curve and showing average and standard-deviation lines.

To produce my random normal samples I used VBA function RandNormalDist by Mike Alexander. I created samples with a mean of 100 and standard deviation of 25, function RandNormalDist(100, 0.25).

The actual mean and standard deviation was 100.84 and 27.49 respectively.

The samples can be checked to confirm normally distributed by comparing the mean, median and mode which should all be equal.

Mean | 100.84 |

Median | 99.5 |

Mode | 89 |

The first thing to do is produce the histogram. This is done by creating bins of a certain width and counting the frequency of the samples that fall in each bin.

Start by calculating the minimum (28) and maximum (184) and then the range (156). Using Sturges’ formula the number of bins is 9, using the square root method the number of bins is 15.

To get a bin width, divide the range (156) by the number of bins (9) which results in 17.33, round this up to an even 20 to produce nice round bin widths.

Set up the bins starting at the minimum and ending at the maximum, using the Excel FREQUENCY function to determine frequency in each bin. The FREQUENCY Function must be entered as an array (ctrl-enter).

Bin | Frequency |

0 | 0 |

20 | 0 |

40 | 1 |

60 | 12 |

80 | 32 |

100 | 56 |

120 | 51 |

140 | 28 |

160 | 19 |

180 | 0 |

200 | 1 |

Using a column chart a histogram can be produced.

Overlaying a normal curve is a little trickier, firstly, the above column chart can’t be used and the histogram must be produced using a scatter chart.

Select the data and produce a scatter chart with smooth lines.

Select the chart and click on the ribbon menu, Layout, then Error Bars and then More Error Bars Options. Select Display Direction Minus, End Style No Cap and Error Amount Percentage 100%.

This will produce a scatter chart with the following error bars.

Increase the Line Style Width so that it starts looking like a histogram with no gaps.

Finally, remove the scatter chart line.

For the normal curve the points need to be created first. The bell curve looks nice when it covers the full 6 standard deviations. 100 points will be created for a nice smooth curve.

Multiply the standard deviation (27.49) by 6 to get 164.96, divide by 100 to get an increment of 1.6496. Starting at minus 3 standard deviations (equal to the mean minus 3 standard deviations (18.36)) increment the value by 1.6496 all the way up to positive 3 standard deviations(183.32). There will actually be 101 total points.

Now for each of those points the normal distribution shall be calculated using Excel’s NORMDIST function. The first parameter is the values we calculated, the second the mean, the third the standard deviation and the last should be FALSE as we don’t want cumulative (NORMDIST(Q1,100.84,27.49,FALSE)).

If you plot the data you will notice a very short normal distribution curve, barely visible as a bell curve due to differences in scale. For our sample of 200 points with bin width of 20, each sample represents a square of 20 by 20. So the total area of our histogram is 200 by 20 which is 4000. The normal distribution has a total area of 1, so the normal curve must be scaled by 4000. And this produces a nice bell-shaped normal curve over the histogram.

You may notice that the histogram and bell curve is a little out of sync, this is due to the way the bins widths and frequencies are plotted. If you have a bin width of 20, and the bin value is 40, the corresponding frequency is all values between 20 and 40. When you plot this value on a scatter chart, the centre of the bar is at 40 and the bar width being plus and minus half the bin width (10), which is 30 to 50 respectively. That’s why the histogram looks shifted to the right.

To fix this, create a temporary fixed bin that has half the bin width (10) subtracted from it and use this when plotting the histogram.

Bin | Frequency | Corrected Bin | Frequency |

0 | 0 | -10 | 0 |

20 | 0 | 10 | 0 |

40 | 1 | 30 | 1 |

60 | 12 | 50 | 12 |

80 | 32 | 70 | 32 |

100 | 56 | 90 | 56 |

120 | 51 | 110 | 51 |

140 | 28 | 130 | 28 |

160 | 19 | 150 | 19 |

180 | 0 | 170 | 0 |

200 | 1 | 190 | 1 |

Since it is a scatter chart, it is possible to add additional indicators including mean and standard deviation lines.

Simply produce a single line segment from 0 to the height of the bell curve using the previous NORMDIST function.

-3sigma | 18.35943 | 0 |

18.35943 | 0.644784 | |

-2sigma | 45.85295 | 0 |

45.85295 | 7.855081 | |

-1sigma | 73.34648 | 0 |

73.34648 | 35.20403 | |

mean | 100.84 | 0 |

100.84 | 58.04164 | |

1sigma | 128.3335 | 0 |

128.3335 | 35.20403 | |

-2sigma | 155.827 | 0 |

155.827 | 7.855081 | |

3sigma | 183.3206 | 0 |

183.3206 | 0.644784 |

Tidying up the colours results in the following final histogram with overlaid normal curve and mean and standard deviation indications.

Here is a quick post to calculate the last business day in a month taking into account holidays.

This is useful for with finance calculations and wanting to know a bank’s last banking day in a month.

=WORKDAY(EOMONTH(A2,0)+1,-1,holidays!A:A)

Where;

- Cell A2 is the reference month cell
- Range holidays!A:A is a list of holidays

A quick post of how to calculate the Stamp Duty of a property with Excel, in my example I am using the Western Australia Residential Rate Dutiable value.

Stamp Duty Cutoff [$] Rate [%] Duty [$] Formula 0 1.9 0 0 120000 2.85 2280 =C3+(A4-A3)*B3/100 150000 3.8 3135 =C4+(A5-A4)*B4/100 360000 4.75 11115 =C5+(A6-A5)*B5/100 725000 5.15 28452.5 =C6+(A7-A6)*B6/100 Property Value 500000 Duty 17765 =VLOOKUP(C9,A:C,3,TRUE) + (C9-VLOOKUP(C9,A:C,1,TRUE))*VLOOKUP(C9,A:C,2,TRUE)/100

**Update, next day**

This also works for Individual income tax rates;

Tax Rates Cutoff [$] Rate [%] Tax [$] Formula 0 0 0 0 6000 15 0 =C3+(A4-A3)*B3/100 37000 30 4650 =C4+(A5-A4)*B4/100 80000 37 17550 =C5+(A6-A5)*B5/100 180000 45 54550 =C6+(A7-A6)*B6/100 Income 100000 Tax 24950 =VLOOKUP(C9,A:C,3,TRUE) + (C9-VLOOKUP(C9,A:C,1,TRUE))*VLOOKUP(C9,A:C,2,TRUE)/100