Shortcut to compute the sum of the numbers

One of the most used purposes of spreadsheet is to compute the totals.
You can use the following shortcut key to arrive at the total instead of putting in the ‘sum’ formula.

Bu holding ‘Alt’ and ’=’ keys together you can get the sum formula automatically for the sequence of the number above.


Note: The sum formula considers the vertical / horizontal range only till there is no empty cell in between. For example in following screen shot, when you enter ‘Alt’ and ‘=’ keys in cell B12, it automatically calculates sum formula range as B8 to B11 and ignores B6 due to line break at B7.

How to compute EMI (equated monthly instalment)?

To compute the amount you need to make periodically to repay the loan together with interest (known as EMI), you can use PMT function of Excel.

This function helps you to calculate the payment for a loan based on constant payment and constant interest rate.

Step by step guide for computation of EMI (equated monthly installment) in Excel 2007

In your ribbon menu, select ‘Formulas’ ribbon. Select ‘Financial’ function and scroll down to PMT

Following are the variables which you need to fill in as a parameter for this function

Rate of Interest: Divide the rate of interest by 12, if annual interest rate is provided and the payment is to be made monthly.
nPer: Number of instalments within which the loan is to be repaid back.
Pv: This is the principal value or the amount of loan taken.
Fv: This is the balance cash amount you want to attain after making the last instalment. This field is optional, if you leave it blank, balance amount is assumed as zero.
Type: Whether payment is going to be made at the beginning of the period (select 1) or at the end of the period (select 0). This field is optional, if you leave it blank, it is assumed as to be paid at the end of the period.

See the screen shot below

Resoure: If you are in hurry, you can use this template

Too many different cell formats

It's for those rare time I hit across the Excel limitation, popping "Too many different cell formats".

Completely confused, tried googling out and to my surprise came across a fact that such error comes when you hit more than 4,000 different cell format. You must be wondering now, how big excel workbook I am working on ;) that says lot about any finance guys.. stuck in the cell of Excel.

If you face similar problem, here's what official site says.

Resize all excel comments in one go

If you are stuck with Excel worksheet having multiple comments, which are not set properly in viewable state, going to each comment and resizing might be a nightmare.

Here's a quick way out...

Step 1. Copy paste the following macro in your workbook or any other excel file, if you don't want to have macro in your original file.

Sub ResizeCommentsInSelection()
Dim mycell As Range
Dim myRng As Range
Dim lArea As Long

Set myRng = Selection

For Each mycell In myRng.Cells
If Not (mycell.Comment Is Nothing) Then
With mycell.Comment
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
.Shape.Height = (lArea / 200) * 1.2
End If
End With
End If
Next mycell
End Sub

Step 2. Select the cells which are having the comments and run the macro.

Voila! you are done.

What are 'Toxic assets' ?

We have been reading recently lot about US Govt plan to bail out financial institutions by buying their 'toxic assets', but what are this toxic assets actually? So let's try and put this term in more simple way.

Let's say that Mike had a loan with Apple Bank. The Loan is for $200,000 for a house and Mike pays 7% interest. The house is collateral, meaning Apple Bank gets the house if Mike defaults. But this house was valued at $275,000, so what is the worry?

So now Apple Bank has "mortgage paper", which is an asset. They can sell the mortgage to anyone they wish. Mike will then be required to pay the purchaser, who will get the benefit of the 7% interest. It's an investment which may (or may not) make more money in the future. A good idea if Apple Bank needs money immediately.

But ol' Mike doesn't have the money to pay this mortgage. At the same time, the house value has greatly reduced to $150,000. Mike still owes $199,000.

If Mike defaults on this, Apple Bank will only be able to recover a portion of their money back. The mortgage paper has now become illiquid (the house can't pay the mortgage). Apple Bank is now unable to sell it. Why would somebody pay for an asset that guarantees you will lose money?

That mortgage has become a "toxic asset".

Here's one youtube video which is also worth watching to understand this term.

Demand and Supply

Click on image to enlarge

Definitions: Repo, Reverse Repo, Bank Rate, Call Rate, CRR & SLR

If you pick any current newspaper, you will find articles talking about economy and RBI monetary policy measures to stimulate economy. I found many non-finance guy actually struggling to figure out the jargons used in this articles. So here's some key monetay policy rates definition, which might help in understanding things better.

Repo (Repurchase) Rate

Repo rate is the rate at which banks borrow funds from the RBI to meet the gap between the demand they are facing for money (loans) and how much they have on hand to lend.

If the RBI wants to make it more expensive for the banks to borrow money, it increases the repo rate; similarly, if it wants to make it cheaper for banks to borrow money, it reduces the repo rate.

Reverse Repo Rate

This is the exact opposite of repo rate.

The rate at which RBI borrows money from the banks (or banks lend money to the RBI) is termed the reverse repo rate. The RBI uses this tool when it feels there is too much money floating in the banking system.

If the reverse repo rate is increased, it means the RBI will borrow money from the bank and offer them a lucrative rate of interest. As a result, banks would prefer to keep their money with the RBI (which is absolutely risk free) instead of lending it out (this option comes with a certain amount of risk)

Consequently, banks would have lesser funds to lend to their customers. This helps stem the flow of excess money into the economy.

Reverse repo rate signifies the rate at which the central bank absorbs liquidity from the banks, while repo signifies the rate at which liquidity is injected.

Bank Rate

This is the rate at which RBI lends money to other banks (or financial institutions .

The bank rate signals the central bank’s long-term outlook on interest rates. If the bank rate moves up, long-term interest rates also tend to move up, and vice-versa.

Banks make a profit by borrowing at a lower rate and lending the same funds at a higher rate of interest. If the RBI hikes the bank rate, the interest that a bank pays for borrowing money (banks borrow money either from each other or from the RBI) increases. It, in turn, hikes its own lending rates to ensure it continues to make a profit.

Call Rate

Call rate is the interest rate paid by the banks for lending and borrowing for daily fund requirement. Since banks need funds on a daily basis, they lend to and borrow from other banks according to their daily or short-term requirements on a regular basis.

CRR

Also called the cash reserve ratio, refers to a portion of deposits (as cash) which banks have to keep/maintain with the RBI. This serves two purposes. It ensures that a portion of bank deposits is totally risk-free and secondly it enables that RBI control liquidity in the system, and thereby, inflation by tying their hands in lending money

SLR

Besides the CRR, banks are required to invest a portion of their deposits in government securities as a part of their statutory liquidity ratio (SLR) requirements. What SLR does is again restrict the bank’s leverage in pumping more money into the economy.