XLS www.yorku.ca

© Beta Management ConsultantsRelease:2.01Input DataInitial Principal100000Annual, nominal interest rate0.06Amortization Period (years)25Initial Date=DATE(2002,7,1)Computed DataEffective Annual Rate (to 2 decimals)=ROUND((1+F12)^12-1,4)Periodic Interest RateAnnual=F12*12Monthly=((1+($F$6/2))^2)^(1/12)-1Months to Amortization=$F$7*12Periodic Payment, Monthly=($F$5*$F$12)/(1-(1+$F$12)^(-$F$13))Rounded to nearest cent=ROUND(F14,2)Rounded to nearest dollar=ROUND(F15,0)DatePaymentInterestPrincipalExtra PaymentsBalance=DATE(YEAR($F$8),MONTH($F$8),DAY($F$8))=$F$5=DATE(IF(MONTH(A20)=12,YEAR(A20)+1,YEAR(A20)),IF(MONTH(A20)=12,1,MONTH(A20)+1),DAY($F$8))=F15=ROUND($F$12*F20,2)=B21-C210=F20-D21-E21=DATE(IF(MONTH(A21)=12,YEAR(A21)+1,YEAR(A21)),IF(MONTH(A21)=12,1,MONTH(A21)+1),DAY($F$8))=$B$21=ROUND($F$12*F21,2)=B22-C220=F21-D22-E22=DATE(IF(MONTH(A22)=12,YEAR(A22)+1,YEAR(A22)),IF(MONTH(A22)=12,1,MONTH(A22)+1),DAY($F$8))=$B$21=ROUND($F$12*F22,2)=B23-C230=F22-D23-E23=DATE(IF(MONTH(A23)=12,YEAR(A23)+1,YEAR(A23)),IF(MONTH(A23)=12,1,MONTH(A23)+1),DAY($F$8))=$B$21=ROUND($F$12*F23,2)=B24-C240=F23-D24-E24=DATE(IF(MONTH(A24)=12,YEAR(A24)+1,YEAR(A24)),IF(MONTH(A24)=12,1,MONTH(A24)+1),DAY($F$8))=$B$21=ROUND($F$12*F24,2)=B25-C250=F24-D25-E25=DATE(IF(MONTH(A25)=12,YEAR(A25)+1,YEAR(A25)),IF(MONTH(A25)=12,1,MONTH(A25)+1),DAY($F$8))=$B$21=ROUND($F$12*F25,2)=B26-C260=F25-D26-E26=DATE(IF(MONTH(A26)=12,YEAR(A26)+1,YEAR(A26)),IF(MONTH(A26)=12,1,MONTH(A26)+1),DAY($F$8))=$B$21=ROUND($F$12*F26,2)=B27-C270=F26-D27-E27=DATE(IF(MONTH(A27)=12,YEAR(A27)+1,YEAR(A27)),IF(MONTH(A27)=12,1,MONTH(A27)+1),DAY($F$8))=$B$21=ROUND($F$12*F27,2)=B28-C280=F27-D28-E28=DATE(IF(MONTH(A28)=12,YEAR(A28)+1,YEAR(A28)),IF(MONTH(A28)=12,1,MONTH(A28)+1),DAY($F$8))=$B$21=ROUND($F$12*F28,2)=B29-C290=F28-D29-E29=DATE(IF(MONTH(A29)=12,YEAR(A29)+1,YEAR(A29)),IF(MONTH(A29)=12,1,MONTH(A29)+1),DAY($F$8))=$B$21=ROUND($F$12*F29,2)=B30-C300=F29-D30-E30=DATE(IF(MONTH(A30)=12,YEAR(A30)+1,YEAR(A30)),IF(MONTH(A30)=12,1,MONTH(A30)+1),DAY($F$8))=$B$21=ROUND($F$12*F30,2)=B31-C310=F30-D31-E31=DATE(IF(MONTH(A31)=12,YEAR(A31)+1,YEAR(A31)),IF(MONTH(A31)=12,1,MONTH(A31)+1),DAY($F$8))=$B$21=ROUND($F$12*F31,2)=B32-C320=F31-D32-E32=DATE(IF(MONTH(A32)=12,YEAR(A32)+1,YEAR(A32)),IF(MONTH(A32)=12,1,MONTH(A32)+1),DAY($F$8))=$B$21=ROUND($F$12*F32,2)=B33-C330=F32-D33-E33=DATE(IF(MONTH(A33)=12,YEAR(A33)+1,YEAR(A33)),IF(MONTH(A33)=12,1,MONTH(A33)+1),DAY($F$8))=$B$21=ROUND($F$12*F33,2)=B34-C340=F33-D34-E34=DATE(IF(MONTH(A34)=12,YEAR(A34)+1,YEAR(A34)),IF(MONTH(A34)=12,1,MONTH(A34)+1),DAY($F$8))=$B$21=ROUND($F$12*F34,2)=B35-C350=F34-D35-E35=DATE(IF(MONTH(A35)=12,YEAR(A35)+1,YEAR(A35)),IF(MONTH(A35)=12,1,MONTH(A35)+1),DAY($F$8))=$B$21=ROUND($F$12*F35,2)=B36-C360=F35-D36-E36=DATE(IF(MONTH(A36)=12,YEAR(A36)+1,YEAR(A36)),IF(MONTH(A36)=12,1,MONTH(A36)+1),DAY($F$8))=$B$21=ROUND($F$12*F36,2)=B37-C370=F36-D37-E37=DATE(IF(MONTH(A37)=12,YEAR(A37)+1,YEAR(A37)),IF(MONTH(A37)=12,1,MONTH(A37)+1),DAY($F$8))=$B$21=ROUND($F$12*F37,2)=B38-C380=F37-D38-E38=DATE(IF(MONTH(A38)=12,YEAR(A38)+1,YEAR(A38)),IF(MONTH(A38)=12,1,MONTH(A38)+1),DAY($F$8))=$B$21=ROUND($F$12*F38,2)=B39-C390=F38-D39-E39=DATE(IF(MONTH(A39)=12,YEAR(A39)+1,YEAR(A39)),IF(MONTH(A39)=12,1,MONTH(A39)+1),DAY($F$8))=$B$21=ROUND($F$12*F39,2)=B40-C400=F39-D40-E40=DATE(IF(MONTH(A40)=12,YEAR(A40)+1,YEAR(A40)),IF(MONTH(A40)=12,1,MONTH(A40)+1),DAY($F$8))=$B$21=ROUND($F$12*F40,2)=B41-C410=F40-D41-E41=DATE(IF(MONTH(A41)=12,YEAR(A41)+1,YEAR(A41)),IF(MONTH(A41)=12,1,MONTH(A41)+1),DAY($F$8))=$B$21=ROUND($F$12*F41,2)=B42-C420=F41-D42-E42=DATE(IF(MONTH(A42)=12,YEAR(A42)+1,YEAR(A42)),IF(MONTH(A42)=12,1,MONTH(A42)+1),DAY($F$8))=$B$21=ROUND($F$12*F42,2)=B43-C430=F42-D43-E43=DATE(IF(MONTH(A43)=12,YEAR(A43)+1,YEAR(A43)),IF(MONTH(A43)=12,1,MONTH(A43)+1),DAY($F$8))=$B$21=ROUND($F$12*F43,2)=B44-C440=F43-D44-E44=DATE(IF(MONTH(A44)=12,YEAR(A44)+1,YEAR(A44)),IF(MONTH(A44)=12,1,MONTH(A44)+1),DAY($F$8))=$B$21=ROUND($F$12*F44,2)=B45-C450=F44-D45-E45=DATE(IF(MONTH(A45)=12,YEAR(A45)+1,YEAR(A45)),IF(MONTH(A45)=12,1,MONTH(A45)+1),DAY($F$8))=$B$21=ROUND($F$12*F45,2)=B46-C460=F45-D46-E46=DATE(IF(MONTH(A46)=12,YEAR(A46)+1,YEAR(A46)),IF(MONTH(A46)=12,1,MONTH(A46)+1),DAY($F$8))=$B$21=ROUND($F$12*F46,2)=B47-C470=F46-D47-E47=DATE(IF(MONTH(A47)=12,YEAR(A47)+1,YEAR(A47)),IF(MONTH(A47)=12,1,MONTH(A47)+1),DAY($F$8))=$B$21=ROUND($F$12*F47,2)=B48-C480=F47-D48-E48=DATE(IF(MONTH(A48)=12,YEAR(A48)+1,YEAR(A48)),IF(MONTH(A48)=12,1,MONTH(A48)+1),DAY($F$8))=$B$21=ROUND($F$12*F48,2)=B49-C490=F48-D49-E49=DATE(IF(MONTH(A49)=12,YEAR(A49)+1,YEAR(A49)),IF(MONTH(A49)=12,1,MONTH(A49)+1),DAY($F$8))=$B$21=ROUND($F$12*F49,2)=B50-C500=F49-D50-E50=DATE(IF(MONTH(A50)=12,YEAR(A50)+1,YEAR(A50)),IF(MONTH(A50)=12,1,MONTH(A50)+1),DAY($F$8))=$B$21=ROUND($F$12*F50,2)=B51-C510=F50-D51-E51=DATE(IF(MONTH(A51)=12,YEAR(A51)+1,YEAR(A51)),IF(MONTH(A51)=12,1,MONTH(A51)+1),DAY($F$8))=$B$21=ROUND($F$12*F51,2)=B52-C520=F51-D52-E52=DATE(IF(MONTH(A52)=12,YEAR(A52)+1,YEAR(A52)),IF(MONTH(A52)=12,1,MONTH(A52)+1),DAY($F$8))=$B$21=ROUND($F$12*F52,2)=B53-C530=F52-D53-E53=DATE(IF(MONTH(A53)=12,YEAR(A53)+1,YEAR(A53)),IF(MONTH(A53)=12,1,MONTH(A53)+1),DAY($F$8))=$B$21=ROUND($F$12*F53,2)=B54-C540=F53-D54-E54=DATE(IF(MONTH(A54)=12,YEAR(A54)+1,YEAR(A54)),IF(MONTH(A54)=12,1,MONTH(A54)+1),DAY($F$8))=$B$21=ROUND($F$12*F54,2)=B55-C550=F54-D55-E55=DATE(IF(MONTH(A55)=12,YEAR(A55)+1,YEAR(A55)),IF(MONTH(A55)=12,1,MONTH(A55)+1),DAY($F$8))=$B$21=ROUND($F$12*F55,2)=B56-C560=F55-D56-E56=DATE(IF(MONTH(A56)=12,YEAR(A56)+1,YEAR(A56)),IF(MONTH(A56)=12,1,MONTH(A56)+1),DAY($F$8))=$B$21=ROUND($F$12*F56,2)=B57-C570=F56-D57-E57=DATE(IF(MONTH(A57)=12,YEAR(A57)+1,YEAR(A57)),IF(MONTH(A57)=12,1,MONTH(A57)+1),DAY($F$8))=$B$21=ROUND($F$12*F57,2)=B58-C580=F57-D58-E58=DATE(IF(MONTH(A58)=12,YEAR(A58)+1,YEAR(A58)),IF(MONTH(A58)=12,1,MONTH(A58)+1),DAY($F$8))=$B$21=ROUND($F$12*F58,2)=B59-C590=F58-D59-E59=DATE(IF(MONTH(A59)=12,YEAR(A59)+1,YEAR(A59)),IF(MONTH(A59)=12,1,MONTH(A59)+1),DAY($F$8))=$B$21=ROUND($F$12*F59,2)=B60-C600=F59-D60-E60=DATE(IF(MONTH(A60)=12,YEAR(A60)+1,YEAR(A60)),IF(MONTH(A60)=12,1,MONTH(A60)+1),DAY($F$8))=$B$21=ROUND($F$12*F60,2)=B61-C610=F60-D61-E61=DATE(IF(MONTH(A61)=12,YEAR(A61)+1,YEAR(A61)),IF(MONTH(A61)=12,1,MONTH(A61)+1),DAY($F$8))=$B$21=ROUND($F$12*F61,2)=B62-C620=F61-D62-E62=DATE(IF(MONTH(A62)=12,YEAR(A62)+1,YEAR(A62)),IF(MONTH(A62)=12,1,MONTH(A62)+1),DAY($F$8))=$B$21=ROUND($F$12*F62,2)=B63-C630=F62-D63-E63=DATE(IF(MONTH(A63)=12,YEAR(A63)+1,YEAR(A63)),IF(MONTH(A63)=12,1,MONTH(A63)+1),DAY($F$8))=$B$21=ROUND($F$12*F63,2)=B64-C640=F63-D64-E64=DATE(IF(MONTH(A64)=12,YEAR(A64)+1,YEAR(A64)),IF(MONTH(A64)=12,1,MONTH(A64)+1),DAY($F$8))=$B$21=ROUND($F$12*F64,2)=B65-C650=F64-D65-E65=DATE(IF(MONTH(A65)=12,YEAR(A65)+1,YEAR(A65)),IF(MONTH(A65)=12,1,MONTH(A65)+1),DAY($F$8))=$B$21=ROUND($F$12*F65,2)=B66-C660=F65-D66-E66=DATE(IF(MONTH(A66)=12,YEAR(A66)+1,YEAR(A66)),IF(MONTH(A66)=12,1,MONTH(A66)+1),DAY($F$8))=$B$21=ROUND($F$12*F66,2)=B67-C670=F66-D67-E67=DATE(IF(MONTH(A67)=12,YEAR(A67)+1,YEAR(A67)),IF(MONTH(A67)=12,1,MONTH(A67)+1),DAY($F$8))=$B$21=ROUND($F$12*F67,2)=B68-C680=F67-D68-E68=DATE(IF(MONTH(A68)=12,YEAR(A68)+1,YEAR(A68)),IF(MONTH(A68)=12,1,MONTH(A68)+1),DAY($F$8))=$B$21=ROUND($F$12*F68,2)=B69-C690=F68-D69-E69=DATE(IF(MONTH(A69)=12,YEAR(A69)+1,YEAR(A69)),IF(MONTH(A69)=12,1,MONTH(A69)+1),DAY($F$8))=$B$21=ROUND($F$12*F69,2)=B70-C700=F69-D70-E70=DATE(IF(MONTH(A70)=12,YEAR(A70)+1,YEAR(A70)),IF(MONTH(A70)=12,1,MONTH(A70)+1),DAY($F$8))=$B$21=ROUND($F$12*F70,2)=B71-C710=F70-D71-E71=DATE(IF(MONTH(A71)=12,YEAR(A71)+1,YEAR(A71)),IF(MONTH(A71)=12,1,MONTH(A71)+1),DAY($F$8))=$B$21=ROUND($F$12*F71,2)=B72-C720=F71-D72-E72=DATE(IF(MONTH(A72)=12,YEAR(A72)+1,YEAR(A72)),IF(MONTH(A72)=12,1,MONTH(A72)+1),DAY($F$8))=$B$21=ROUND($F$12*F72,2)=B73-C730=F72-D73-E73=DATE(IF(MONTH(A73)=12,YEAR(A73)+1,YEAR(A73)),IF(MONTH(A73)=12,1,MONTH(A73)+1),DAY($F$8))=$B$21=ROUND($F$12*F73,2)=B74-C740=F73-D74-E74=DATE(IF(MONTH(A74)=12,YEAR(A74)+1,YEAR(A74)),IF(MONTH(A74)=12,1,MONTH(A74)+1),DAY($F$8))=$B$21=ROUND($F$12*F74,2)=B75-C750=F74-D75-E75=DATE(IF(MONTH(A75)=12,YEAR(A75)+1,YEAR(A75)),IF(MONTH(A75)=12,1,MONTH(A75)+1),DAY($F$8))=$B$21=ROUND($F$12*F75,2)=B76-C760=F75-D76-E76=DATE(IF(MONTH(A76)=12,YEAR(A76)+1,YEAR(A76)),IF(MONTH(A76)=12,1,MONTH(A76)+1),DAY($F$8))=$B$21=ROUND($F$12*F76,2)=B77-C770=F76-D77-E77=DATE(IF(MONTH(A77)=12,YEAR(A77)+1,YEAR(A77)),IF(MONTH(A77)=12,1,MONTH(A77)+1),DAY($F$8))=$B$21=ROUND($F$12*F77,2)=B78-C780=F77-D78-E78=DATE(IF(MONTH(A78)=12,YEAR(A78)+1,YEAR(A78)),IF(MONTH(A78)=12,1,MONTH(A78)+1),DAY($F$8))=$B$21=ROUND($F$12*F78,2)=B79-C790=F78-D79-E79=DATE(IF(MONTH(A79)=12,YEAR(A79)+1,YEAR(A79)),IF(MONTH(A79)=12,1,MONTH(A79)+1),DAY($F$8))=$B$21=ROUND($F$12*F79,2)=B80-C800=F79-D80-E80If you want a longer amortization schedule, simply copy the last line of the tabledown as many times as you wish. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download