Question

6. In cell K4, create a formula using the IF function to calculate the interest paid on the mortgage (or the difference between the total payments made each year and the total amount of mortgage principal paid each year). a. The formula should first check if the value in cell H4 (the balance remaining on the loan each year) is greater than 0. b. If the value in cell H4 is greater than 0, the formula should return the value in J4 subtracted from the value in cell D5 multiplied by 12. Use a relative cell reference to cell J4 and an absolute cell reference to cell D5. (Hint: Use 12*$D$5-J4 as the is_true argument value in the formula.) c. If the value in cell H4 is not greater than 0, the formula should return a value of 0.

Answer 1

Answer: IF(H4>0,12*$D$5-J4,0)

From the question, we are used to create a formula to calculate an interest paid on mortgage using the IF function.

We will start by giving a brief analysis on how to go about this for better understanding. This should help us get to our desired result.

If( H4>0 is the logical test,

then

12*$D$5-J4 is [value_if_true]

0 is [value_if_false]  

so we have : IF(H4>0,12*$D$5-J4,0)

Using this above formula and typing it into cell K4;

• we are able to first check if the value in H4 is greater than 0.
• if is greater,it returns a value in J4  subtracted from the value in cell D5 multiplied by 12
• but if it is not, it returns a value of 0

[Note] :Absolute referencing of cell D5 means that this will not change while extending this formula to the entire column. Whereas, since cell H4 and J4 have relative cell referencing, these will change relatively as we extend the formula to the entire column.

cheers i hope this helps