There are several tools used to conduct financial analysis. One is a database of accounting information, in which an analyst can roam for days, tracking down the details regarding when specific transactions have taken place, why they occurred, and the likelihood of their happening again. However, analysts rarely descend straight into the depths of the accounting database without first using some more simple means for determining what problem has arisen, which yields clues regarding where in the database to search. This higher-level information is obtained by using ratio and trend analysis to pinpoint the issue. To get this information, a calculator, pencil, and paper are sufficient, but also very time consuming and prone to error. Instead, an electronic spreadsheet is the best method.
Excel can be used to solve a problem, as long as a sample situation is provided. A key issue that is noted is the difference between a “spreadsheet” and a “worksheet“. In Excel, a spreadsheet can have a number of interlinked layers known as worksheets. When an entry is made in one worksheet, it can be referenced by other worksheets in the same spreadsheet. This is a preferable approach to using Excel for financial analysis, since one can separate the data being analyzed in one worksheet, ratios in another, and graphics in yet another worksheet – but with formulas linking all of them together.
One is sometimes called upon to calculate the result of a variety of payment or receipt scenarios that involve streams of cash flows over multiple periods. In this post, we review several of the most common ones, as well as how to use Excel formulas to create accurate answers for each scenario. They are:
If a company is investing money at a consistent rate for a fixed time period, one may want to know how much that investment stream will be worth at a specified future date. To determine the future value of such an investment stream, use the “FV formula“.
The Interest Rate component is the expected earnings rate on the investment. The Number of Periods component is the number of periods over which a fixed amount is being invested. The Payment Amount component is the fixed amount being paid during each period.
EXAMPLE: If a company were to invest $4,000 per period for 128 months at an annual interest rate of 8%, the formula would be: FV(8%/12,128,-4000).
Interest Rate On An Annuity
If a company is offered a specific set of regular payments (i.e., an annuity), it is useful to see if this results in an adequate rate of return. To determine the interest rate on such an annuity, use the “RATE formula“.
THE FORMULA: RATE(Number of Periods, Payment Amount, Present Value of Payments).
The Number of Periods component specifies the number of periods over which fixed payments will be made. The Payment Amount component shows the amount of each payment. The Present Value of Payments component notes the current cash value of the investment for which payments are being made.
EXAMPLE: If an investor wishes to purchase a bond with a current value of $100,000 and will pay for it with monthly payments of $1,500 for 10 years, the formula would be: RATE(10,–1500,100000).
The resulting interest rate will be on a monthly basis, and must be multiplied by twelve to arrive at an annual interest rate.
One of the most common calculations one makes is the determination of loan payments for a specified amount of borrowing at a set interest rate. This calculation can also be used to verify the same information that has been supplied by the lender. To determine the amount of a loan payment, use the “PMT formula“.
THE FORMULA: PMT(Interest Rate, Number of Payments, Present Value of Loan).
The Interest Rate component is the interest rate per period, and is assumed not to vary. The Number of Payments component represents the number of periods over which fixed payments are to be made. The Present Value of Loan component is the original amount of the loan that must now be paid off.
EXAMPLE: To determine the payment amount for a loan of $355,000 at an interest rate of 7%, and which will be paid off in 120 periods (e.g., 10 years), the formula would be: PMT(7%/12,120,355000).
Number Of Periods
If there is an obligation to pay a set amount with a standard number of periodic payments, it may be necessary to determine how long those payments will last before the obligation to pay has been fulfilled. To determine the number of required payments, use the “NPER formula“.
THE FORMULA: NPER(Interest Rate, Payment Amount, Present Value).
The Interest Rate component is the interest rate per period, and is assumed not to vary. The Payment Amount component is the amount paid per period, and is also assumed not to vary. Finally, the Present Value component is the current value of the obligation being paid off.
EXAMPLE: To determine the number of payment periods required to pay off an investment of $150,000 at an annual interest rate of 12%, and with periodic payments of $28,134, the formula is: NPER(12%,–28134,150000).
If a company anticipates receiving a string of payments in future periods, it may be useful to determine their present value, since this information can be used to compare the payment stream to the value of other sources of income to see which is more valuable. Such an analysis may result in the sale of whatever investment is resulting in a payment stream that is considered to have a less-than-stellar present value. To determine an investment’s present value, use the “PV formula“.
THE FORMULA: PV(Interest Rate, Number of Payment Periods, Total Payment Made per Period).
The Interest Rate component is the interest rate per period, and is assumed not to vary. The Number of Payment Periods component is the number of periods during which payments are expected to arrive. The Total Payment Made per Period component is the payment made in each period, and is assumed to be the same in every period.
EXAMPLE: To determine the present value of a stream of $10,000 payments at a 6.5% interest rate over 20 years, the formula would be: PV(6.5%,20,10000).
A company sometimes makes a fixed payment without a clear delineation of what portion is interest and what is principal. This is especially common for capital leases, where the lessor is under no legal obligation to reveal how much of either payment component is being paid. To determine the amount of principal within such a payment, use the “PPMT formula“.
THE FORMULA: PPMT(Interest Rate, Period, Total Number of Payment Periods, Present Value).
The Interest Rate component is the interest rate per period. The Period component is the specific period for which you want to determine the principal payment. The Total Number of Payment Periods component is the number of payment periods for the term of the entire loan. Finally, the Present Value component is the amount of the loan at the beginning of the transaction.
EXAMPLE: If you want to determine the total principal payment in the seventh year of a 12-year, $50,000 loan that bears a 9% interest rate, the formula would be: PPMT(9%,7,12,50000).
Given the number of available Excel formulas, it is evident that the majority of queries that one will receive regarding the time value of money can be answered by a short formula entry in Excel spreadsheet.