Time Value of Money Spreadsheet
Calculating Time Value of Money
Have you every wondered how your EMI (Equated Monthly Installment) is
calculated when you apply for a housing loan? The Handspring line of Palm
PDAs (including Treo 600/650) have an enhanced calculator which provides an
excellent application for calculating "Time Value of Money". The Handspring
calculator application even presents a nice Amortization schedule (which
gives the principal and interest portions of each EMI payment). I looked at
many calculator applications for Linux and Windows and never came across a
single application which was so convenient to use.
Time Value of Money Spreadsheet
I did find several spreadsheets that give you an Amortization schedule (many
of them available as Microsoft Templates) but they still don't give the
flexibility of this simple app. So I wrote one of my own. I wrote it in
Gnumeric but the
spreadsheet is available here in Excel format for
You need to fill the columns in the blue background. The spreadsheet
computes the columns in yellow and creates an amortization table on the
right. The spreadsheet is useful not just for loans but for any kind of
periodic payments, for example, a recurring deposit in a bank. Here are the
explanations of the columns you need to fill.
Beginning: Set to one if payments are made at the beginning of
the period. For recurring investments you normally want to calculate
from the first day you made the investment. For loans this should be
zero because you'll usually pay at the end of the period, i.e. one month
from the date you took the loan.
Payments/Year: Number of payments you make in a year. This
value is usually twelve.
Present Value: This is the value of money in your hand
today. In other words, this is the amount of loan you want to take. Set
it to zero if you're calculating future value of your investments. Leave
it blank if you want to compute the loan you can get given the interest
rate, EMI and the number of payments.
Future Value: This is the future value of your investments. Set
it to zero if you're calculating payments for your loan. Leave it blank
if you want to compute the future value of your investments given the
interest rate, periodic payment and the number of payments.
Payment: This is the EMI you're paying out of your pocket for a
loan or the periodic investment you're making out of your pocket. Leave
it blank if you want to compute the EMI given the interest rate, amount
of loan and number of payments. You can also use this to compute the
amount of periodic payments you need to accumulate a lump sum. Payments
should be negative numbers because money is going out of your packet.
Interest Rate: This is the annual interest rate (APR in US
terminology). Leave it blank if you know the EMI and want to verify the
interest rate the bank is quoting you. Especially useful for personal
loans where bank gives you a low "flat" interest.
Number of Payments: This is the total number of payments you
will make. Leave it blank in case you know the other values and want to
compute how many payments you need to make. This is useful for example
when your Bank increases/reduces your interest rate without leaving the