Interest Simple and Compound
Article Index
Interest Simple and Compound
Present and future value
Compound interest
Financial functions
An investment/loan spreadsheet
Inflation
Inflation and interest
Summary and key points
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.

Banner

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.


fig5

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.

fig6

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.

Banner

<ASIN:1430218983>

<ASIN:0471779725>



 
 

   
RSS feed of all content
I Programmer - full contents
Copyright © 2014 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.