Search This Blog
Friday, March 6, 2026
On the limits of VaR
Sunday, February 23, 2025
Reflection: 1968, 1998, and 2025?
Thursday, February 25, 2021
How to calculate average rates earned on a lump sum?
Imagine you invest $10,000 this year in a fund that promises the following returns over the next 5 years:
Your money would grow thusly.
It would be wrong to simple average the rates per year since this ignores any compounding. We use the function "RATE" from excel.
Wrong approach: (10 + 8 + 6 + 4 +2) / 5 = 6%
Correct approach: =RATE(5 , 0 , -10000 , 13358.44) = 5.962%
Sunday, February 14, 2021
assigning a value based on price
Dear students,
I was asked for help with the grading tab, specifically on how to weigh each exam based on the performance. The example below should help. Is about allocating money according to price.
Saturday, February 13, 2021
Using vlookup in a range
Imagine you are looking for the perfect sparkling wine. You have a database that looks like this.
Your function should tell excel: Go look at the price that is stored in cell "G2", get it and find it in "Table array". Once you find it, bring the value stored in column 2 of "Table array".
One problem is that vlookup can not understand "$15 - up". vlookup can look for only 1 value, so the first thing you want to do is to change those price ranges to a single price that vlookup could read. Like the one below.
Excel will look place every price between $0 and $7 as "Cheap", anything between $7 and $15 as value... and so on. So in practice you create ranges in a vertical way.
The formula would look like this.
Tuesday, September 22, 2020
How to calculate the total interest paid over the life of a loan?
Assume you are going to buy a house that is for sale at $500,000. You have saved $100,000 for downpayment so the mortage will be for $400,000.
The bank quotes you two options. A 30 year, monthly payments loan at 6% or a 15 year loan at 5%.
Your friends tell you that if you go 15 years the total interest paid will be hundreds of thousands less.
You don't believe them so decide to do the math.
Easy way. Not surprisingly, there is an app for that. Excel has a formula.
CUMIPMT(rate, nper, pv, start_period, end_period, type)
For the 30 year loan it would be
CUMIPMT(6%/12 , 12*30 , $400,000, 1, 360, 0)
Hard way. Do an amortization table and add all interest payments.
Curious about the answer?
Check the spreadsheet, you can make a copy and change it to your needs.
Monday, September 21, 2020
What is a Dutch auction and how does it work?
A Dutch auction is way of offering/selling of goods to the public. The seller offers the lot to the general public. The actual price of the whole set is determined after all offers have been received at the level at which all shares could be sold. The public or buyers are supposed to state how many items thye want and at what price do they want them.
The following example would illustrate it.
Assume the seller is trying to sell 10,000 shares of Ford Motor Company (F).
Buyers submit their orders...
Merida -- 1,500 shares at $15.00 each
Peter Pan --- 2,500 shares at $14.25 each
Pop - Eye -- 2,500 shares at $14.00 each
Donald Duck --3,500 shares at $13.90 each
Kim Possible -- 4,000 shares at $13.85 each
Ben Ten -- 3,000 shares at $13,77 each.
... ...
In this case, the 10,000 shares are sold to Merida, Peter Pan, Pop Eye and Donald Duck for $13.90. The whole lot can be sold.





