{"text_format":"3","workbook":"xlsx","n_sheets":1,"sheets":["APR Calculation"],"not_rendered":{"defined_names":1,"comments":1},"read":"whole","date_system":"1900"}

{"sheet":"APR Calculation","index":0,"n_rows":54,"n_cols":14,"rows_kept":54,"merged":[[0,0,0,13]]}
["Calculation of Total Cost of Credit and APR of a Payday Loan"]
["LENDER: TBD","BORROWER: TBD",{"$nulls":3},"Loan Contract Date: YYYY-MM-DD"]
["Inspector: TBD","Date of Analysis: YYYY-MM-DD"]
["9) Loan Data Analysis Result"]
["Loan Amount OK?",{"$f":"IF(B16>1500, \"NO\", \"YES\")","v":"YES"},"Not more than $1,500?",null,null,"N.B.",null,"denotes a user-entered  data field (unprotected cells)"]
["Loan Term OK?",{"$f":"IF(B39>62, \"NO\", \"YES\")","v":"YES"},"Not longer than 62 days?",{"$nulls":4}," denote a calculated field - data cannot be directly entered into the field by the user (protected cells)"]
["Total Cost of Credit OK?",{"$f":"IF(C26>14%, \"NO\", \"YES\")","v":"YES"},"Not greater than either $210 or 14%?",{"$nulls":4}," "]
["Declared APR OK?",{"$f":"IF(+B53<B42*0.99875,\"NO\",IF(B53>B42*1.00125,\"NO\",\"YES\"))","v":"YES"},"Declared APR agrees with calculated APR?",{"$nulls":5},"Instructions"]
["Payday loan OK?",{"$f":"IF(B5=\"NO\",\"NO\", IF(B6=\"NO\",\"NO\", IF(B7=\"NO\",\"NO\", IF(B8=\"NO\",\"NO\", \"YES\"))))","v":"YES"},"Based on the above factors",null,null,"1)","Review the example.  When you are ready to enter your data, select all the green cells in the worksheet and then click"]
[{"$nulls":6},"\"Delete\" to remove all the example data from the unprotected cells of the worksheet."]
["1) Value Received by Borrower","Amount",{"$nulls":3},"2)","When entering dollar amounts, the precision required under the legislation (1/8 of 1%) means you must enters cents"]
["Includes:",{"$nulls":5},"as well as dollars."]
["    money received by borrower",{"v":300,"d":"300.00"},"If applicable",null,null,"3)","Enter the amount of the \"values received\" by borrower in area 1)."]
["    money paid to 3rd party for benefit of borrower",null,"If applicable",null,null,"4)","Enter costs of credit charged to borrower in area 2)."]
["    existing debt to another creditor paid off for borrower",null,"If applicable",null,null,"5)","If a balance is still claimed as owed by the borrower, enter in area 3)."]
["Total value received by borrower (the principal of the loan)",{"$f":"SUM(B13:B15)","v":{"v":300,"d":"300.00"}},{"$nulls":3},"6)","Enter the earliest and latest activity dates for the loan in mm/dd/yyyy format, one set according to the contract"]
[{"$nulls":6},"and the other being the actual dates involved for the loan in area 4)."]
["2) Cost of Credit",{"$nulls":4},"7)","Enter the 3 interest rates that could be included in the loan contract in area 4)."]
["Includes:",{"$nulls":4},"8)","To prove the stated cost of credit and APR for the contract, in area 5) enter the repayment dates and amounts,"]
["   loan fee",{"v":42,"d":"42.00"},"If applicable",{"$nulls":3},"starting with the earliest one first and then subsequent ones in chronological order.  Unneeded cells should be blank "]
["   brokerage fee",null,"If applicable",{"$nulls":3},"(contents deleted) rather than zero-filled."]
["    admin fees",null,"If applicable",null,null,"9)","The spreadsheet will automatically calculate the cost of credit and APR and whether or not the loan would be over or"]
["    documentation fees",null,"If applicable",{"$nulls":3},"underpaid, based on the data (area 6).  The table at the top of the sheet (area 9) will report \"YES\" if the loan is within the"]
["    non-default interest (total $ amount)",null,"If applicable",{"$nulls":3},"regulatory parameters."]
["    other fee or charge_____________________________",null,"If applicable",null,null,"10)","If the repayment arrangement set out in the contract would result in an over or underpayment of the debt, the overage"]
["Total cost of credit (By amount & percent)",{"$f":"SUM(B20:B25)","v":{"v":42,"d":"42.00"}},{"$f":"+B26/B16","v":{"v":0.14,"d":"14.00%"}},{"$nulls":3},"or underage will be add/deducted from the cost of credit and an adjusted APR will be calculated."]
[{"$nulls":5},"11)","Repeat 8) above in the \"Actual Payments\" area 6) to determine the actual cost of credit, APR and balance owed or owing"]
["Total amount of original indebtedness",{"$f":"+B16+B26","v":{"v":342,"d":"342.00"}},{"$nulls":4},"for the latest payment date entered.  List the date and amount of each payment made by the borrower in chronological"]
[{"$nulls":6},"order."]
["3) Value Given by the Borrower",{"$nulls":4},"12)","The whole worksheet will print on one sheet of letter-size paper."]
["Includes:",{"$nulls":4},"13)","If you want to save the worksheet, you must rename it (include the investigation file number in the new name)."]
["    money paid or transferred by borrower to creditor",{"v":0,"d":"0.00"},"Complete Repayment Schedules below"]
["    balance still outstanding & owed by borrower",null,"If applicable"]
["       Total value given or still owed by borrower",{"$f":"SUM(B32:B33)","v":{"v":0,"d":"0.00"}}]
[]
["4) Loan Data","contract","Actual"]
["date of first advance to borrower(yyyy/mm/dd)","2025-04-01","2025-04-01"]
["date of last repayment by borrower(yyyy/mm/dd)","2025-04-15","2025-04-15"]
["number of days of term",{"$f":"+B38-B37","v":{"v":14,"d":"14.00"}},{"$f":"+C38-C37","v":{"v":14,"d":"14.00"}}]
["T=length of the term in years",{"$f":"+B39/365","v":{"v":0.038356164383561646,"d":"0.0384"}},{"$f":"+C39/365","v":{"v":0.038356164383561646,"d":"0.0384"}}]
[]
["Declared APR of loan per contract",{"v":3.65,"d":"365.00%"},"As disclosed in loan contract"]
["Non-default contract interest (part of 15% cost of credit)",null,"As disclosed in loan contract, if applicable"]
["Default (arrears) contract interest (maximum 30%)",null,"As disclosed in loan contract, if applicable"]
[]
["Payment Schedule","Date of Payment (Enter as mm/dd/yyyy or Leave Blank-Not Zero-filled)","Amount of Payment (Enter Payment as a Negative Amount or Zero)","Weighing Factor for Period for outstanding principal","Calculated Weighed Average of Principal for APR Calculation-Beginning","Principal Outstanding Beginning of Period","Permissible Chges O/S Beginning of Period","Accrued Interest Owing on Principal Debt for Period","Accrued Arrears Interest Owing on Principal Debt for Period","Unpaid Accrued Interest (Regular plus Arrears)","Total Amount of Debt Outstanding End of Period","Interest Paid for Period (Interest plus Arrears Interest, if applicable)","Non Interest Charges Paid during Period","Principal Paid during Period"]
["5) Scheduled Payments",{"$f":"+B37","v":"2025-04-01"},{"$nulls":3},{"$f":"+$B$16","v":{"v":300,"d":"300.00"}},{"$f":"+$B$26-$B$24","v":{"v":42,"d":"42.00"}},{"$nulls":3},{"$f":"+F47+G47+H47","v":{"v":342,"d":"342.00"}}]
["   (in chronological order)","2025-04-15",{"v":-342,"d":"(342.00)"},{"$f":"IF(ISBLANK(B48),0,+(B48-B47)/365)","v":{"v":0.038356164383561646,"d":"0.0384"}},{"$f":"IF(ISBLANK(B48),0,+D48*F48)","v":{"v":11.506849315068493,"d":"11.51"}},{"$f":"+F47+N47","v":{"v":300,"d":"300.00"}},{"$f":"+G47+M47","v":{"v":42,"d":"42.00"}},{"$f":"IF(ISBLANK(B48),0,F48*$B$43*((B48-B47)/365))","v":{"v":0,"d":"0.00"}},null,null,{"$f":"+F48+G48+H48+L48+M48+N48","v":{"v":0,"d":"0.00"}},{"$f":"IF(C48=0,0,IF((ABS(C48)<=H48),C48,-H48))","v":{"v":0,"d":"0.00"}},{"$f":"IF((-C48+L48)<=G48, C48-L48, -G48)","v":{"v":-42,"d":"(42.00)"}},{"$f":"IF((-C48+L48+M48)=0,0,+C48-L48-M48)","v":{"v":-300,"d":"(300.00)"}}]
[{"$nulls":3},{"$f":"IF(ISBLANK(B49),0,+(B49-B48)/365)","v":{"v":0,"d":"0.0000"}},{"$f":"IF(ISBLANK(B49),0,+D49*F49)","v":{"v":0,"d":"0.00"}},{"$f":"+F48+N48","v":{"v":0,"d":"0.00"}},{"$f":"+G48+M48","v":{"v":0,"d":"0.00"}},{"$f":"IF(ISBLANK(B49),0,F49*$B$43*((B49-B48)/365))","v":{"v":0,"d":"0.00"}},null,null,{"$f":"+F49+G49+H49+L49+M49+N49","v":{"v":0,"d":"0.00"}},{"$f":"IF(C49=0,0,IF((ABS(C49)<=H49),C49,-H49))","v":{"v":0,"d":"0.00"}},{"$f":"IF((-C49+L49)<=G49, C49-L49, -G49)","v":{"v":0,"d":"0.00"}},{"$f":"IF((-C49+L49+M49)=0,0,+C49-L49-M49)","v":{"v":0,"d":"0.00"}}]
[{"$nulls":3},{"$f":"IF(ISBLANK(B50),0,+(B50-B49)/365)","v":{"v":0,"d":"0.0000"}},{"$f":"IF(ISBLANK(B50),0,+D50*F50)","v":{"v":0,"d":"0.00"}},{"$f":"+F49+N49","v":{"v":0,"d":"0.00"}},{"$f":"+G49+M49","v":{"v":0,"d":"0.00"}},{"$f":"IF(ISBLANK(B50),0,F50*$B$43*((B50-B49)/365))","v":{"v":0,"d":"0.00"}},null,null,{"$f":"+F50+G50+H50+L50+M50+N50","v":{"v":0,"d":"0.00"}},{"$f":"IF(C50=0,0,IF((ABS(C50)<=H50),C50,-H50))","v":{"v":0,"d":"0.00"}},{"$f":"IF((-C50+L50)<=G50, C50-L50, -G50)","v":{"v":0,"d":"0.00"}},{"$f":"IF((-C50+L50+M50)=0,0,+C50-L50-M50)","v":{"v":0,"d":"0.00"}}]
["6) Calculation Results - per contract",null,{"$f":"SUM(C48:C50)","v":{"v":-342,"d":"(342.00)"}},null,{"$f":"SUM(E48:E50)","v":{"v":11.506849315068493,"d":"11.51"}},null,null,{"$f":"SUM(H48:H50)","v":{"v":0,"d":"0.00"}},null,null,{"$f":"+K50","v":{"v":0,"d":"0.00"}},{"$f":"SUM(L48:L50)","v":{"v":0,"d":"0.00"}},{"$f":"SUM(M48:M50)","v":{"v":-42,"d":"(42.00)"}},{"v":-300,"d":"(300.00)"}]
["Borrower's Balance Owing/(Owed) As At",{"$f":"+B38","v":"2025-04-15"},{"$f":"+K50","v":{"v":0,"d":"0.00"}},{"$nulls":8},"Total Cost of Credit - per contract",{"$f":"-L51-M51","v":{"v":42,"d":"42.00"}}]
["Calculated APR (Annual Percentage Rate) - per contract",{"$f":"$B$26/($E$51)","v":{"v":3.65,"d":"365.00%"}}]
["   Adjusted APR recognizing ending balance owing/(owed)",{"$f":"($B$26-C52)/($E$51)","v":{"v":3.65,"d":"365.00%"}}]