|
Page 5 of 8
An investment/loan spreadsheet
It is very convenient to have a spreadsheet that you can use to calculate the unknown fourth quantity involved in an investment or loan given any three of them.
This is very easy using the table of formulas listed above. A column of entered values can be created next to a column of formulas that calculate each value from the remaining three. This description is easier to understand with reference to Figure 5.
Figure 5
Enter the labels in column A and in B1 and C1 as shown but do not enter any values in column B yet.
The following formulas need to be entered into column C:
| Calculate |
Cell |
Formula |
| Investment PV |
C2 |
=PV(B3/12,B4*12,0,-B5) |
| Interest Rate |
C3 |
=RATE(B4*12,0,-B2,B5)*12 |
| Term in years |
C4 |
=NPER(B3/12,0,-B2,B5)/12 |
| Return FV |
C5 |
=FV(B3/12,B4*12,0,-B2) |
Notice the need to multiply by 12 to convert the calculated monthly rate into a per annum rate and to divide by 12 to convert the term in months into years.
The final touch is to format B2..C2 and B5..C5 as currency and B3..C3 as percentage with two decimal places. Now when you enter any three of the values into column B the fourth will be calculated in column C.
Initially, because we have not typed anything in column B for the Entered values when the spreadsheet attempts to work out the Calculated values, the resulting is a mix of zeros and error meesages.
This highlights a problem with this spreadsheet - as we only want it to work out one formula at a time the unused ones will show spurious results or suggest there are errors due to the incomplete data in column B.
The solution is to test if each entered value is zero and only calculate a value if it is, on the assumption that if the user hasn’t supplied one then it must be needed.This means surrounding the formulas in column C with IF statements that do the calculation when the cell to the left is zero and display a blank cell otherwise.
| Calculate |
Cell |
Formula |
| Investment PV |
C2 |
=IF(B2=0,PV(B3/12,B4*12,0,-B5),"") |
| Interest Rate |
C3 |
=IF(B3=0,RATE(B4*12,0,
-B2,B5)*12,"") |
| Term in years |
C4 |
=IF(B4=0,NPER(B3/12,0,
-B2,B5)/12,"") |
|
Return FV
|
C5 |
=IF(B5=0,FV(B3/12,B4*12,0,-B2),"") |
Now once three quantities are entered the remaining one will be calculated as in Figure 6.
Figure 6: Enter PV, Interest Rate and FV and Term is calculated
An affordable loan
Although there is a tendency to think of this spreadsheet and its related calculations in terms of investment where the interest is re-invested it is worth keeping in mind that it also applies to loans where the interest is `rolled up’ into the total debt to be repaid.
For example, if you know that you can afford to repay $1000 (and presumably make a reasonable profit) at the end of a 3 year project how much can you borrow at 20% per annum. Using the spreadsheet with FV equal to $1000 quickly gives the answer $551.53.
<ASIN:1430218983>
<ASIN:0471779725>
|
