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 :
a. monthly
N=
I%=
PV=
PMT=
FV=
P/Y=
C/Y=
b. bi -weekly
N=
I%=
PV=
PMT=
FV=
P/Y=
C/Y=
c.weekly
N=
I%=
PV=
PMT=
FV=
P/Y=
C/Y=
d. design a spreadsheet to calculate the interest he would have paid with each repayment option
monthly repayments:
bi-weekly repayments :
weekly repayments :
e. what formula would you use in the spreadsheet to determine the interest paid par weekly payment ?
D:
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.
http://www.calculator.net/amortization-calculator.html?cloanamount=20000&cloanterm=4&cinterestrate=4.31109416711&printit=0&x=50&y=23
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 .
