financial-modeling 7

Firstly, believe that Excel can do anything. It’s true. Excel is a functional programming language. Not with the same power as some programming languages, maybe. But power is just a way of making a little go a long way (power = succinctness, according to Paul Graham). And Fred Brooks, in No Silver Bullet, argues: I believe the single most powerful software-productivity strategy for many organizations today is to equip the computer-naive intellectual workers who are on the firing line with personal computers and good generalized writing, drawing, file, and spreadsheet programs and then to turn them loose. ...

Recently, I was helping a bank define Basel 2 requirements. For every dollar a bank lends, at least 8 cents should come from its own pocket, and the rest from its depositors. But a risky $1 loan may be like a $1.5 loan, whereas a $1 Government loan may be like a $0.5 loan. This is the "risk-weighted asset" (RWA) value. Basel 2 says 8% of risk-weighted assets should come from the bank's pocket. ...

Banks install ATMs to lower their branch costs, and to attract new customers. When working out the economics of ATMs, we found that lowering branch costs alone could not be a viable reason to install an ATM. The bank argued as follows: “Every time someone withdraws money from an ATM, they avoid going to the branch. With enough people going to the ATM, I can afford not to increase my branch size, and that saves me money. Since it costs me Rs 20 every time a person withdraws cash (in terms of salary, rent, etc.) and an ATM costs about Rs 2,200 a day, I’ll break even if there are 110 cash withdrawals from the ATM.” ...

A corollary of Rule 3: Never type the same formula twice. Design the formula so that if you cut and paste it elsewhere, it works correctly. The $ symbol and the F4 key for cell references help in 90% of the cases. For complex requirements and large data, 5 functions come in handy: INDIRECT, OFFSET, ADDRESS, ROW and COLUMN. I once did a survey, and had data spread across 300 sheets (same format on all sheets). I needed cell D3 across all sheets in a column, to summarise the results. The image explains what I did. ...

I spend a lot of time building models on Excel. I have 4 rules that help me get things done fast. Never use the mouse. The keyboard is much faster. Never type in data. You can always import it. Avoid manual labour. Use Excel to automate the task. Make your data visually obvious. Let’s look at Rule #1: Never use the mouse. Using the keyboard can be 10 times faster than the mouse. It takes time to move one hand from the keyboard to the mouse, locate the item you want to click at, move the mouse there, adjust it finely so it’s pointing at the exact spot, and then click it. For example, to insert text without formatting, I’d just go Alt-E, S, enter. It takes half a second. It took me 5 seconds with a mouse. (I timed 10 continuous attempts in both cases.) ...

Asian option calculators from Enron and DerivativesModels.com.

Prof. Apte mentioned that the currency forward is not an unbiased estimator of the future spot rate, but is the certainty equivalent of it. The Kelly FAQ gives a good description of what certainty equivalence means.