I need help with D please, I think i am putting in the interest formula wrong...
i think its =20000(or cell #)*((0.0435/2+1)^(2*4)-1)
ross borrowed $20000 in a secured line of credit to finish his basement . the loan had an annual rate of 4.35 % compounded semi annually and he wanted to pay it off in four years . what would his payments be if he paid :
b. bi -weekly
d. design a spreadsheet to calculate the interest he would have paid with each repayment option
bi-weekly repayments :
weekly repayments :
e. what formula would you use in the spreadsheet to determine the interest paid par weekly payment ?
The first thing you have to do is to convert the interest rate from compounded semi -annually to compounded monthly, bi-weekly and weekly. If you did it correctly, you should get the following:
Compounded monthly =4.31109416711%
Compounded bi-weekly =4.3069320008%
Compounded weekly =4.30514985512%
I pesonally do not use "Spreadsheets", so I cannot tell you exactly how to set it up on a Spreadsheet. But here is an amortization schedule which should be similar to a Spreadsheet. All you have to do is to copy the format from this schedule to your Spreadsheet. I have done only the MONTHLY amortization schedule, because this calculator is designed to do ONLY monthly payments.
Look at the 1st payment of this amortization schedule and copy it for bi-weekly and weekly schedules. This is how the 1st payment is broken down:
Take the above interest compounded monthly:4.31109416711/1200 =0.00359257847259 x 20,000=$71.85. This is interest on the loan for the 1st.month as you can see from schedule.
Look at the top of the schedule and you will see "Monthly pay =$454.37". You would subtract the interest payment of $71.85 from this and you would get:$454.37 - $71.85 =$$382.52. This is principal payment, which you will deduct from the loan: $20,000 - $382.52 =$19,617.48, which is the balance of the loan after the 1st payment. And so on.....
Thank you for reading my question! i am pretty sure its asking about monthly, weekly and biweekly payments, while the compounds stay at 2..... is it not?
Yes, they are asking you to make a spreadsheet for all three options:
Monthly payments, which I have already given you.
Bi-Weekly payments, which you have to calculate and make a spreadsheet for all 4 x 26 =104 payments.
Weekly payments, which again yo have to calculate and make a spreadsheet for all 4 x 52 =208 payments.
Using the above rate of bi-weekly compound, the bi-weekly payments would be=$209.51. Then you will have to make a spreadsheet for 104 payments and add up all the interest he would have paid, which should come to a total of =$209.51 x 104 - $20,000 =$1,789.04.
Do the same thing for a weekly compound, and the weekly payments should be =$104.71. Then you will have to make a spreadsheet for 208 payments and add up all the interest he would have paid, which should come to a total of=$104.71 x 208 - $20,000 =$1,779.68 .