{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Prompt Pay Calculator","Interest Factor Background Info"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Prompt Pay Calculator","index":0,"n_rows":37,"n_cols":6,"rows_kept":37,"cut":false,"hidden":false,"merged":[[0,0,0,8],[27,5,28,8],[36,5,39,8],[1,0,2,8]]}
["STATE OF WISCONSIN - PROMPT PAYMENT CALCULATION FORM"]
["This workbook has been prepared in accordance with s. 16.528(2), Wis. Stats., to assist agencies in the calculation of prompt payment interest amounts.  Consult Wisconsin Accounting Manual Section 05-02 for further guidance."]
[]
["Customer Identification:"]
[]
[null,"Supplier Name:"]
[null,"Payment Remit to Address:"]
[]
[null,"Invoice Number:"]
[]
["STAR Identification:"]
[]
[null,"Agency Number:"]
[null,"Voucher Number:"]
[]
["Interest Calculation Dates:"]
[]
[null,"Date Invoice Received:"]
[null,"Payment Date*:"]
[]
["Interest Due Calculation:"]
[]
[null,"Invoice Amount:"]
[null,"Number of Days Late:",null,null,{"$f":"(_xlfn.DAYS(E19,E18))-30","v":-30}]
[]
[null,"Prompt Pay Interest Factor:",null,null,{"$f":"(1.01)^(($E$24/30)-((MOD($E$24,30))/30))*((($E$24-(30*(($E$24/30)-((MOD($E$24,30))/30))))/360)*0.12)+((1.01)^(($E$24/30)-((MOD($E$24,30))/30))-1)","v":-9.9009900990099098E-3}]
[]
[null,"Prompt Pay Interest Due:",null,null,{"$f":"E23*E26","v":0},"Use Account Code 7855000 - Interest Paid under prompt payment law"]
[]
[null,"Total Due:",null,null,{"$f":"SUM(E28,E23)","v":0}]
[]
[]
[null,null,null,null,"Input Field"]
[]
[null,null,null,null,"Calculated Field"]
[]
[null,null,null,null,null,"* The Payment Date is the date the check pays, this can be found in the \"Payments\" tab of the AP Voucher Screen"]

{"sheet":"Interest Factor Background Info","index":1,"n_rows":49,"n_cols":6,"rows_kept":49,"cut":false,"hidden":false,"merged":[[21,4,22,8],[17,1,18,9]]}
[null,null,null,null,null,"Compound Interest Factors for Prompt Pay"]
[]
[]
[null,"The prompt pay statute (s. 16.528(2), Wis. Stats.) requires compounding of interest on a monthly basis."]
[null,"The formula below can be used to calculate the factor to determine the amount of interest owed."]
[]
[null,null,"(1+i)^n*(((T-30)*n)/360)*a+((1+i)^n-1)"]
[]
[null,"i = Monthly interest rate"]
[null,"n = # of whole months the payment is subject to interest expense (a month is 30 days)"]
[null,"T = Total number of days subject to interest. (# days late)."]
[null,"a = Annual interest rate"]
[null,"S = n times 30."]
[]
[null,"Rather than using the formula to calculate the interest factor the following chart can be"]
[null,"used to obtain the factors for a given period."]
[]
[null,"Multiply the interst factor for the days late from the chart below times the amount owed to determine the amount of interest owed."]
[]
[]
[null,"Variable","Value",null,"Instructions"]
[null,"T",0,null,"In the shaded cell to the right of variable T enter the number of days the payment is late."]
[null,"a",0.12]
[null,"i",{"$f":"C23/12","v":0.01},null,"All other fields will be filled automatically."]
[null,"n",{"$f":"INT(C22/30)","v":0},null,"Do not enter amounts in the fields for \"a, i, n, or"]
[null,"S",{"$f":"+C25*30","v":0},null,"S\"."]
[]
[null,"Days","Interest",null,"Days","Interest"]
[null,"Late","Factor",null,"Late","Factor"]
[null,{"$f":"C26","v":0},{"$f":"(1+$C$24)^$C$25*((($C$26-30*$C$25)/360)*$C$23)+((1+$C$24)^$C$25-1)","v":0},null,{"$f":"+B49+1","v":20},{"$f":"(1+$C$24)^$C$25*(((E30-30*$C$25)/360)*$C$23)+((1+$C$24)^$C$25-1)","v":6.6666666666666662E-3}]
[null,{"$f":"+B30+1","v":1},{"$f":"(1+$C$24)^$C$25*(((B31-30*$C$25)/360)*$C$23)+((1+$C$24)^$C$25-1)","v":3.3333333333333332E-4},null,{"$f":"+E30+1","v":21},7.0000000000000001E-3]
[null,2,6.6666666666666664E-4,null,22,7.3333333333333332E-3]
[null,3,1E-3,null,23,7.6666666666666654E-3]
[null,4,1.3333333333333333E-3,null,24,8.0000000000000002E-3]
[null,5,1.6666666666666666E-3,null,25,8.3333333333333332E-3]
[null,6,2E-3,null,26,8.6666666666666663E-3]
[null,7,2.3333333333333331E-3,null,27,8.9999999999999993E-3]
[null,8,2.6666666666666666E-3,null,28,9.3333333333333324E-3]
[null,9,3.0000000000000001E-3,null,29,9.6666666666666672E-3]
[null,10,3.3333333333333331E-3,null,30,9.9999999999999985E-3]
[null,11,3.6666666666666666E-3]
[null,12,4.0000000000000001E-3]
[null,13,4.3333333333333331E-3]
[null,14,4.6666666666666662E-3]
[null,15,4.9999999999999992E-3]
[null,16,5.3333333333333332E-3]
[null,17,5.6666666666666662E-3]
[null,18,6.0000000000000001E-3]
[null,19,6.3333333333333332E-3]