Question

To evaluate the refinancing offer for Dave and Jana, you can build a spreadsheet model using the Excel PMT function. The PMT function helps calculate the payment for a loan with constant payments and a constant interest rate. The arguments for this function are as follows:

rate: the monthly interest rate for the loan.
nper: the total number of payments.
pv: the present value (the amount borrowed).
fv: the future value (desired cash balance after the last payment, usually 0).
type: payment type (0 for end of the period, 1 for beginning of the period).
For Dave and Jana's original loan, there will be 180 payments (12 months multiplied by 15 years, which is 180 months). To calculate the monthly payment, you would use the formula =PMT(0.0549/12, 180, 230415, 0, 0), which equals $1,881.46. Note that because payments are made monthly, the annual interest rate must be expressed as a monthly rate, and for payment calculations, it is assumed that the payment is made at the end of the month.

The savings from refinancing will occur over time, and you need to discount them back to their current value. You can use the formula K(1+r)^t-1 for converting K dollars saved t months from now to current dollars, where r is the monthly inflation rate. For this calculation, assume that r = 0.002, and Dave and Jana make their payment at the end of each month.

Use your spreadsheet model to calculate the savings in current dollars associated with the refinanced loan compared to staying with the original loan.

Answer 1

The monthly payments on a $300,000 loan at 6% interest over 30 years are calculated using a present value loan payment formula. Extra payments can reduce total interest and loan duration. Changes in interest rates affect the present value of future payments, with higher rates leading to a lower present value.

Step-by-step explanation:

To calculate the monthly payments for a $300,000 loan at 6% interest with monthly compounding over 30 years, we use the formula PV = R * [1 - (1+i)^(-n)] / i, where PV is the present value (loan amount), R is the payment, i is the monthly interest rate, and n is the total number of payments. For a 6% annual interest rate, the monthly interest rate (i) is 0.06/12, and the number of payments (n) for 30 years is 12*30.

To see how extra payments impact the loan, we assume one extra payment per year (making it effectively 13 monthly payments per year). This reduces the amount of interest paid over time and shortens the loan period. To discount future savings back to their present value, we account for inflation by using a slightly modified formula, accounting for extra payments and inflation.

For example, if a bond's present value calculation changes due to a rise in the interest rate from 8% to 11%, the present discounted value of future payments decreases even though the actual dollar payments remain unchanged. This is because the present value of future payments is inversely related to the discount rate.