I have already submitted once so I know that what I turned in was wrong but I can't figure out what my formulas should be. I know it needs the Ipmt and the ppmt function but when I tried that it did not end up being right.
| Enter a function in cell D12, based on the payment and loan details, that calculates the amount of interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. |
| Enter a function in cell E12, based on the payment and loan details, that calculates the amount of principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. |
| Enter a formula in cell F12 to calculate the remaining balance after the current payment. The remaining balance is calculated by subtracting the principal payment from the balance in column B. |
| Enter a function in cell G12, based on the payment and loan details, that calculates the amount of cumulative interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references. |
| Enter a function in cell H12, based on the payment and loan details, that calculates the amount of cumulative principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references.  |
Beta Manufacturing Facility Amortization Table Payment Details Payment $6,000.00 APR 5.75% Years 5 Pmts per Year 12 Loan Details Loan $312,227.32 Periodic Rate 0.479% # of Payments 60 Payment Number 1 NH 3 4 5 6 7 8 9 wung w sng9 Beginning Payment Principal Remaining Cumulative Cumulative Balance Amount Interest Paid Repayment Balance Interest Principal $312,227.32 $6,000.00 $ 1,496.09 $ 649.77 $311,577.55 $ 1,496.09 $ 4,503.91 $311,577.55 $6,000.00 $ 1,471.44 $ 687.13 $310,890.43 $ 2,964.42 $ 9,010.61 $310,890.43 $6,000.00 $ 1,446.60 $ 726.64 $310,163.79 $ 4,404.48 $ 13,518.45 $310,163.79 $6,000.00 $ 1,421.58 $ 768.42 $309,395.37 $ 5,815.76 $ 18,025.62 $309,395.37 $6,000.00 $ 1,396.36 $ 812.60 $ 308,582.77 $ 7,197.72 $ 22,530.18 $308,582.77 $6,000.00 $ 1,370.95 $ 859.33 $ 307,723.44 $ 8,549.76 $ 27,030.02 $307,723.44 $6,000.00 $ 1,345.35 $ 908.74 $ 306,814.70 $ 9,871.30 $ 31,522.85 $306,814.70 $6,000.00 $ 1,319.55 $ 960.99 $ 305,853.71 $ 11,161.71 $ 36,006.19 $305,853.71 $6,000.00 $ 1,293.56 $ 1,016.25 $304,837.46 $ 12,420.31 $ 40,477.37 $304,837.46 $6,000.00 $ 1,267.37 $ 1,074.68 $303,762.78 $ 13,646.41 $ 44,933.50 $303,762.78 $6,000.00 $ 1,240.98 $ 1,136.48 $302,626.31 $ 14,839.28 $ 49,371.45 $302,626.31 $6,000.00 $ 1,214.40 $ 1,201.82 $301,424.48 $ 15,998.16 $ 53,787.84 $301,424.48 $6,000.00 $ 1,187.62 $ 1,270.93 $300,153.55 $ 17,122.24 $ 58,179.02 $300,153.55 $6,000.00 $ 1,160.64 $ 1,344.01 $298,809.55 $ 18,210.68 $ 62,541.05 $298,809.55 $6,000.00 $ 1,133.46 $ 1,421.29 $297,388.26 $ 19,262.60 $ 66,869.70 $297,388.26 $6,000.00 $ 1,106.09 $ 1,503.01 $295,885.25 $ 20,277.07 $ 71,160.39 $295,885.25 $6,000.00 $ 1,078.53 $ 1,589.43 $ 294,295.81 $ 21,253.12 $ 75,408.17 $294,295.81 $6,000.00 $ 1,050.78 $ 1,680.83 $ 292,614.99 $ 22,189.73 $ 79,607.73 $292,614.99 $6,000.00 $ 1,022.84 $ 1,777.47 $290,837.51 $ 23,085.84 $ 83,753.34 14 15 Beta Manufacturing Facility Amortization Table Payment Details =PMT(E7, E8,-E6) 0.0575 Loan Payment APR Years Pmts per Year 3 Loan Details 312227.318047974 =B7/B9 =B8*B9 Periodic Rate # of Payments 5 12 Payment Number 1 2 Beginning Balance =E6 =F12 =F13 =F14 3 4 5 6 7 =F15 =F16 =F17 =F18 =F19 Payment Amount =B6 =C12 =C13 =C14 =C15 =C16 =C17 =C18 =C19 =C20 Interest Paid Principal Repayment Remaining Balance =IPMT($E$7,412,$E$8,-$B12,0) =PPMT($B$7, A12,$E$8,-$B$1 =B12-E12 =IPMT($E$7,A13,$E$8,-$B13,0) =PPMT($B$7,413,$E$8,-$B$1 =B13-E13 =IPMT($E$7,414,$E$8,-$B14,0) =PPMT($B$7, A14,$E$8,-$B$1 =B14-E14 =IPMT($E$7, A15, $E$8,-$B15,0) =PPMT($B$7,415,$E$8,-$B$1 =B15-E15 =IPMT($E$7, A16,$E$8,-$B16,0) =PPMT($B$7,A16, $E$8,-$B$1 =B16-E16 =IPMT($E$7,417,$E$8,-$B17,0) =PPMT($B$7,417,$E$8,-$B$1 =B17-E17 =IPMT($E$7,418,$E$8,-$B18,0) =PPMT($B$7, A18, $E$8,-$B$1 =B18-E18 =IPMT($E$7,A19,$E$8,-$B19,0) =PPMT($B$7,A19,$E$8,-$B$1 =B19-E19 =IPMT($E$7,A20,$E$8,-$B20,0) =PPMT($B$7,A20, $E$8,-$B$1 =B20-E20 =IPMT($E$7,A21, $E$8,-$B21,0) =PPMT($B$7,A21,$E$8,-$B$1 =B21-E21 Cumulative Interest Cumulative Principal --CUMIPMT($E$7,$E$8,B12,$A$1 --CUMPRINC($E$7,$E$8,812,$A$ --CUMIPMT($E$7,$E$8,813,$A$1 --CUMPRINC($E$7,$E$8,B13, $A$ --CUMIPMT($E$7,$E$8,B14,$A$1 --CUMPRINC($E$7,$E$8,814,$A$ --CUMIPMT($E$7,$E$8,315,$A$1 =-CUMPRINC($E$7,$E$8,815,$A$ --CUMIPMT($E$7,$E$8,816,$A$1 --CUMPRINC($E$7,$E$8,816,$A$ --CUMIPMT($E$7,$E$8,017,$A$1 --CUMPRINC($E$7,$E$8,817,$A$ --CUMIPMT($E$7,$E$8,818, $A$1 --CUMPRINC($E$7,$E$8,818,$A$ --CUMIPMT($E$7,$E$8,319,$A$1 =-CUMPRINC($E$7,$E$8,319,$A$ --CUMIPMT($E$7,$E$8,B20,$A$1 --CUMPRINC($E$7,$E$8,B20,$A$ --CUMIPMT($E$7,$E$8,821,$A$1 =-CUMPRINC($E$7,$E$8,821, $A$ 8 09 10 =F20 Beta Manufacturing Facility Amortization Table Payment Details Payment $6,000.00 APR 5.75% Years 5 Pmts per Year 12 Loan Details Loan $312,227.32 Periodic Rate 0.479% # of Payments 60 Payment Number 1 NH 3 4 5 6 7 8 9 wung w sng9 Beginning Payment Principal Remaining Cumulative Cumulative Balance Amount Interest Paid Repayment Balance Interest Principal $312,227.32 $6,000.00 $ 1,496.09 $ 649.77 $311,577.55 $ 1,496.09 $ 4,503.91 $311,577.55 $6,000.00 $ 1,471.44 $ 687.13 $310,890.43 $ 2,964.42 $ 9,010.61 $310,890.43 $6,000.00 $ 1,446.60 $ 726.64 $310,163.79 $ 4,404.48 $ 13,518.45 $310,163.79 $6,000.00 $ 1,421.58 $ 768.42 $309,395.37 $ 5,815.76 $ 18,025.62 $309,395.37 $6,000.00 $ 1,396.36 $ 812.60 $ 308,582.77 $ 7,197.72 $ 22,530.18 $308,582.77 $6,000.00 $ 1,370.95 $ 859.33 $ 307,723.44 $ 8,549.76 $ 27,030.02 $307,723.44 $6,000.00 $ 1,345.35 $ 908.74 $ 306,814.70 $ 9,871.30 $ 31,522.85 $306,814.70 $6,000.00 $ 1,319.55 $ 960.99 $ 305,853.71 $ 11,161.71 $ 36,006.19 $305,853.71 $6,000.00 $ 1,293.56 $ 1,016.25 $304,837.46 $ 12,420.31 $ 40,477.37 $304,837.46 $6,000.00 $ 1,267.37 $ 1,074.68 $303,762.78 $ 13,646.41 $ 44,933.50 $303,762.78 $6,000.00 $ 1,240.98 $ 1,136.48 $302,626.31 $ 14,839.28 $ 49,371.45 $302,626.31 $6,000.00 $ 1,214.40 $ 1,201.82 $301,424.48 $ 15,998.16 $ 53,787.84 $301,424.48 $6,000.00 $ 1,187.62 $ 1,270.93 $300,153.55 $ 17,122.24 $ 58,179.02 $300,153.55 $6,000.00 $ 1,160.64 $ 1,344.01 $298,809.55 $ 18,210.68 $ 62,541.05 $298,809.55 $6,000.00 $ 1,133.46 $ 1,421.29 $297,388.26 $ 19,262.60 $ 66,869.70 $297,388.26 $6,000.00 $ 1,106.09 $ 1,503.01 $295,885.25 $ 20,277.07 $ 71,160.39 $295,885.25 $6,000.00 $ 1,078.53 $ 1,589.43 $ 294,295.81 $ 21,253.12 $ 75,408.17 $294,295.81 $6,000.00 $ 1,050.78 $ 1,680.83 $ 292,614.99 $ 22,189.73 $ 79,607.73 $292,614.99 $6,000.00 $ 1,022.84 $ 1,777.47 $290,837.51 $ 23,085.84 $ 83,753.34 14 15 Beta Manufacturing Facility Amortization Table Payment Details =PMT(E7, E8,-E6) 0.0575 Loan Payment APR Years Pmts per Year 3 Loan Details 312227.318047974 =B7/B9 =B8*B9 Periodic Rate # of Payments 5 12 Payment Number 1 2 Beginning Balance =E6 =F12 =F13 =F14 3 4 5 6 7 =F15 =F16 =F17 =F18 =F19 Payment Amount =B6 =C12 =C13 =C14 =C15 =C16 =C17 =C18 =C19 =C20 Interest Paid Principal Repayment Remaining Balance =IPMT($E$7,412,$E$8,-$B12,0) =PPMT($B$7, A12,$E$8,-$B$1 =B12-E12 =IPMT($E$7,A13,$E$8,-$B13,0) =PPMT($B$7,413,$E$8,-$B$1 =B13-E13 =IPMT($E$7,414,$E$8,-$B14,0) =PPMT($B$7, A14,$E$8,-$B$1 =B14-E14 =IPMT($E$7, A15, $E$8,-$B15,0) =PPMT($B$7,415,$E$8,-$B$1 =B15-E15 =IPMT($E$7, A16,$E$8,-$B16,0) =PPMT($B$7,A16, $E$8,-$B$1 =B16-E16 =IPMT($E$7,417,$E$8,-$B17,0) =PPMT($B$7,417,$E$8,-$B$1 =B17-E17 =IPMT($E$7,418,$E$8,-$B18,0) =PPMT($B$7, A18, $E$8,-$B$1 =B18-E18 =IPMT($E$7,A19,$E$8,-$B19,0) =PPMT($B$7,A19,$E$8,-$B$1 =B19-E19 =IPMT($E$7,A20,$E$8,-$B20,0) =PPMT($B$7,A20, $E$8,-$B$1 =B20-E20 =IPMT($E$7,A21, $E$8,-$B21,0) =PPMT($B$7,A21,$E$8,-$B$1 =B21-E21 Cumulative Interest Cumulative Principal --CUMIPMT($E$7,$E$8,B12,$A$1 --CUMPRINC($E$7,$E$8,812,$A$ --CUMIPMT($E$7,$E$8,813,$A$1 --CUMPRINC($E$7,$E$8,B13, $A$ --CUMIPMT($E$7,$E$8,B14,$A$1 --CUMPRINC($E$7,$E$8,814,$A$ --CUMIPMT($E$7,$E$8,315,$A$1 =-CUMPRINC($E$7,$E$8,815,$A$ --CUMIPMT($E$7,$E$8,816,$A$1 --CUMPRINC($E$7,$E$8,816,$A$ --CUMIPMT($E$7,$E$8,017,$A$1 --CUMPRINC($E$7,$E$8,817,$A$ --CUMIPMT($E$7,$E$8,818, $A$1 --CUMPRINC($E$7,$E$8,818,$A$ --CUMIPMT($E$7,$E$8,319,$A$1 =-CUMPRINC($E$7,$E$8,319,$A$ --CUMIPMT($E$7,$E$8,B20,$A$1 --CUMPRINC($E$7,$E$8,B20,$A$ --CUMIPMT($E$7,$E$8,821,$A$1 =-CUMPRINC($E$7,$E$8,821, $A$ 8 09 10 =F20
