{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["المقسطون","تسديد عميل"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":127,"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":"المقسطون","index":0,"n_rows":62,"n_cols":14,"rows_kept":62,"cut":false,"hidden":false,"merged":[[22,2,22,5],[13,9,13,10],[2,5,2,7],[4,5,4,6],[6,5,6,6],[16,5,16,6],[3,0,4,2],[22,12,22,13],[3,11,4,13],[3,8,4,9],[15,8,15,11],[11,8,12,9],[14,9,14,10]]}
[]
[null,null,null,null,null,null,null,null,null,"تاريخ اليوم :",{"$f":"TODAY()","v":"2021-08-19"}]
[null,null,"ملف العميــل",2300,"الاســم ",{"$f":"VLOOKUP(D3,B26:K61,2,0)","v":23}]
["نظـام الخصــم ",null,null,null,null,null,null,null,"حساب نســبة التقسـيط ",null,null,"نظام تحويل التاريخ من هجري لميلادي "]
[null,null,null,null,"إسم البضاعة :"]
[null,null,"السعر النقدي",{"$f":"VLOOKUP(D3,B26:K61,4,0)","v":45000},null,null,null,null,"السعر","الاشهر",null,"بداية التسديد ","نهاية التسديد"]
[null,null,"قيمة العقد بالتقسيط",{"$f":"VLOOKUP(D3,B26:K61,5,0)","v":65250},"موديلها :",null,null,null,50000,{"$f":"VLOOKUP(D3,B26:K61,3,1)","v":40},null,{"$f":"D11","v":"2018-04-10"},{"$f":"D12","v":"2021-06-05"}]
[null,null,"المقدم",0,null,null,null,null,"الزيادة  ",{"$f":"SUM(I7*(J7%+5%))","v":22500},null,{"$f":"TEXT(L7,\"[$-,197000]B2yyyy/mm/dd;@\")+1","v":"2018-04-11"},{"$f":"TEXT(M7,\"[$-,197000]B2yyyy/mm/dd;@\")+1","v":"2021-06-06"}]
[null,null,"القسط الشهري",{"$f":"VLOOKUP(D3,B26:K61,6,0)","v":1631.25},null,null,null,null,"بعد الزيادة",{"$f":"SUM(I7+J8)","v":72500},null,{"$f":"TEXT(L7,\"[$-,197]B2yyyy/mm/dd;@\")","v":"1439/07/24"},{"$f":"TEXT(M7,\"[$-,197]B2yyyy/mm/dd;@\")","v":"1442/10/24"}]
[null,null,"ماتم سداده من المبلغ",{"$f":"IF($D$11>$K$2,0,IF($D$12<$K$2,$D$7,(SUM(DATEDIF($D$11,$K$2,\"Y\")*12)+(DATEDIF($D$11,$K$2,\"ym\"))+(IF(DATEDIF($D$11,$K$2,\"md\")<>0,1,0)))*D9))","v":65250},null,"المدة",{"$f":"(D7-D8)/D9","v":40},null,"القسط الشهري",{"$f":"SUM(J9/J7)","v":1812.5}]
[null,null,"بداية التسديد",{"$f":"VLOOKUP(D3,B26:K61,7,0)","v":"2018-04-10"},null,"الربح كامل",{"$f":"D7-D6","v":20250}]
[null,null,"نهاية التسديد",{"$f":"VLOOKUP(D3,B26:K61,8,0)","v":"2021-06-05"},null,"الربح الشهري",{"$f":"G11/G10","v":506.25},null,"التاريخ الميلادي والتاريخ الهجري"]
[null,null,"الأشهر المسددة",{"$f":"(D10/D9)","v":40},null,"النسبة ",{"$f":"(G11/(D6-D8))*100","v":45}]
[null,null,"الأشهر المتبقية",{"$f":"D16/D9","v":0},null,"النسبة السنوية",{"$f":"(G13/G10)*12","v":13.5},null,"ملف العميــل","الاســم",null,"مدة العقـد","من ","الى"]
[null,null,null,null,null,"النسبة الشهرية",{"$f":"G13/G10","v":1.125},null,{"$f":"D3","v":2300},{"$f":"F3","v":23},null,{"$f":"G10","v":40},{"$f":"D11","v":"2018-04-10"},{"$f":"D12","v":"2021-06-05"}]
[null,null,"الباقي من المبلغ",{"$f":"D7-D10","v":0},null,null,null,null,"حالة الاشهر وقيمة العقد",null,null,null,"2019-11-01",{"$f":"EDATE(M16,L15)","v":"2023-03-01"}]
[null,null,"الخصم",{"$f":"((D16*G14%)*(D14/12))/(1+((G14%)*(D14/12)))","v":0},null,null,null,null,"الأشهر المسددة",{"$f":"IF(DATEDIF(M15,NOW(),\"m\")+1>L15,L15,DATEDIF(M15,NOW(),\"m\")+1)","v":40},"المبــالغ المســدة",{"$f":"(J17*D9)","v":65250},"حـالة العقــد","المبلغ المسدد"]
[null,null,"المطلوب",{"$f":"D16-D17","v":0},null,null,null,null,"الأشهر المتبقية",{"$f":"L15-J17","v":0},"المبــالغ المتبـقية",{"$f":"(J18*D9)","v":0},{"$f":"VLOOKUP(D3,B26:N61,11,0)","v":"سداد مبكر"}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"VLOOKUP(D4,B27:L62,11,0)","v":{"$error":"#N/A"}},{"$error":"#N/A"}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$error":"#N/A"},{"$error":"#N/A"}]
[null,null,"ملاحظة : اذا تم السداد قبل الموعد المحدد يتم إضافة 1000 ( الف ريال )"]
[]
[null,"رقم الملف","الاسم","مدة القرض ( بالشهر ) ","السعر النقدي","القيمة بعد التقسيط","القسط ","من","الى","الربــح ","الدخل الشهري","حـالة العقـد","سداد مبكر بتاريخ","المبلغ المسدد"]
[null,100,1,40,45000,46400,1160,"2008-10-25",{"$f":"MID(TEXT(H26,\"b2dd.mm.yyyy\"),1,2)-MID(TEXT(H26+ROUND((D26-1)*354.367056/12,0),\"b2dd.mm.yyyy\"),1,2)+H26+ROUND((D26-1)*354.367056/12,0)","v":"2011-12-20"},{"$f":"SUM(E26*(D26%+5%))","v":20250},0,{"$f":"IFERROR(IF(AND(M26<>\"\",M26<I26),\"سداد مبكر\",IF(TODAY()=I26,\"العقد انتهى اليوم\",IF(TODAY()>I26,\"العقـد منتهى\",IF(TODAY()<H26,\"لم يتم تداولة\",\"العقـد سارى\")))),\"\")","v":"العقـد منتهى"}]
[null,200,2,40,45000,43500,1087,"2009-01-21","2012-03-18",20250,0,{"$f":"IFERROR(IF(AND(M27<>\"\",M27<I27),\"سداد مبكر\",IF(TODAY()=I27,\"العقد انتهى اليوم\",IF(TODAY()>I27,\"العقـد منتهى\",IF(TODAY()<H27,\"لم يتم تداولة\",\"العقـد سارى\")))),\"\")","v":"العقـد منتهى"}]
[null,300,3,40,45000,46400,1160,"2010-03-10","2013-05-05",20250,0,"العقـد منتهى"]
[null,400,4,40,45000,39160,979,"2010-06-07","2013-08-01",20250,0,"العقـد منتهى"]
[null,500,5,40,45000,43520,1088,"2010-11-01","2013-12-28",20250,0,"العقـد منتهى"]
[null,600,6,40,45000,69024,5507,"2010-12-31","2014-02-25",20250,0,"العقـد منتهى"]
[null,700,7,40,45000,38124,2118,"2011-01-30","2014-03-26",20250,0,"العقـد منتهى"]
[null,800,8,40,45000,57592,2504,"2011-07-26","2014-09-19",20250,0,"العقـد منتهى"]
[null,900,9,40,45000,58000,1450,"2011-07-26","2014-09-19",20250,0,"العقـد منتهى"]
[null,1000,10,40,45000,58000,1450,"2011-07-26","2014-09-19",20250,0,"العقـد منتهى"]
[null,1100,11,40,45000,58000,1450,"2011-08-24","2014-10-19",20250,0,"العقـد منتهى"]
[null,1200,12,40,45000,58000,1450,"2011-08-24","2014-10-19",20250,0,"العقـد منتهى"]
[null,1300,13,40,45000,76850,1921,"2011-11-21","2015-01-15",20250,0,"العقـد منتهى"]
[null,1400,14,40,45000,62360,1559,"2012-02-17","2015-04-14",20250,0,"العقـد منتهى"]
[null,1500,15,40,45000,60900,1523,"2012-03-18","2015-05-13",20250,0,"العقـد منتهى"]
[null,1600,16,40,45000,50760,1269,"2014-06-23","2017-08-17",20250,0,"العقـد منتهى"]
[null,1700,17,40,45000,50760,1269,"2014-10-19","2017-12-13",20250,0,"العقـد منتهى"]
[null,1800,18,40,45000,53640,{"$f":"F43/D43","v":1341},"2015-01-15","2018-03-12",20250,0,"العقـد منتهى"]
[null,1900,19,40,45000,60920,1523,"2015-05-13","2018-07-08",20250,0,"العقـد منتهى"]
[null,2000,20,40,45000,60750,1518.75,"2015-07-11","2018-09-05",20250,0,"العقـد منتهى"]
[null,2100,21,40,45000,105624,2640.6,"2017-02-21","2020-04-18",20250,0,"العقـد منتهى"]
[null,2200,22,40,45000,{"$f":"E47+J47","v":65250},1631.25,"2018-01-12","2021-03-08",20250,0,"العقـد منتهى"]
[null,2300,23,40,45000,{"$f":"E48+J48","v":65250},1631.25,"2018-04-10","2021-06-05",20250,0,"سداد مبكر","2019-02-07",39889]
[null,2400,24,35,50000,{"$f":"E49+J49","v":70000},{"$f":"F49/D49","v":2000},"2019-11-01","2022-08-01",20000,{"$f":"G49","v":2000},"العقـد سارى"]
[null,2500,25,35,50000,{"$f":"E50+J50","v":70000},{"$f":"F50/D50","v":2000},"2020-09-25",{"$f":"MID(TEXT(H50,\"b2dd.mm.yyyy\"),1,2)-MID(TEXT(H50+ROUND((D50-1)*354.367056/12,0),\"b2dd.mm.yyyy\"),1,2)+H50+ROUND((D50-1)*354.367056/12,0)","v":"2023-06-26"},{"$f":"SUM(E50*(D50%+5%))","v":20000},{"$f":"G50","v":2000},"العقـد سارى"]
[null,2600,26,35,50000,{"$f":"E51+J51","v":70000},2000,"2021-03-27",{"$f":"MID(TEXT(H51,\"b2dd.mm.yyyy\"),1,2)-MID(TEXT(H51+ROUND((D51-1)*354.367056/12,0),\"b2dd.mm.yyyy\"),1,2)+H51+ROUND((D51-1)*354.367056/12,0)","v":"2023-12-26"},20000,{"$f":"G51","v":2000},"العقـد سارى"]
[null,null,null,null,null,null,{"$f":"F52/40","v":0}]
[null,null,null,null,null,null,0]
[null,null,null,null,null,null,0]
[null,null,null,null,null,null,0]
[null,null,null,null,null,null,0]
[null,null,null,null,null,null,0]
[null,null,null,null,null,null,0]
[null,null,null,null,null,null,0]
[null,null,null,null,null,null,0]
[null,null,null,null,null,null,0]
[null,null,null,null,{"$f":"SUM(E26:E61)","v":1185000},{"$f":"SUM(F26:F61)","v":1538784},null,null,null,{"$f":"SUM(J26:J61)","v":525750},{"$f":"SUM(K26:K61)","v":6000}]

{"sheet":"تسديد عميل","index":1,"n_rows":58,"n_cols":12,"rows_kept":58,"cut":false,"hidden":false,"merged":[[14,2,16,3],[10,7,10,8],[11,7,11,8],[12,6,12,9],[13,10,13,11],[14,10,14,11]]}
[]
[]
[]
[]
[]
[]
[null,"ملف العميــل",2300,null,null,"تاريخ اليوم :",{"$f":"TODAY()","v":"2021-08-19"}]
[null,null,null,"القســـــــط ",{"$f":"VLOOKUP(C7,المقسطون!$B$25:$H$60,6,0)","v":1631.25}]
[null,"الاســـــم",{"$f":"VLOOKUP('تسديد عميل'!C7,المقسطون!B25:I60,2,0)","v":23},"السعر النقدي ",{"$f":"VLOOKUP(C7,المقسطون!$B$25:$H$60,4,0)","v":45000}]
[null,null,null,"الربـــــــــح ",{"$f":"VLOOKUP(C7,المقسطون!B25:K60,9,0)","v":20250}]
[null,"مدة القرض (بالاشهر)",{"$f":"VLOOKUP(C7,المقسطون!$B$25:$H$60,3,0)","v":40},"قيمة القرض",{"$f":"VLOOKUP(C7,المقسطون!B25:I60,5,0)","v":65250},null,"ملف العميــل","الاســم",null,"مدة العقـد","من ","الى"]
[null,"تاريخ بداية القرض",{"$f":"VLOOKUP(C7,المقسطون!$B$25:$H$60,7,0)","v":"2018-04-10"},"نسبة السداد بـ  %",{"$f":"E13/E11","v":1},null,{"$f":"C7","v":2300},{"$f":"C9","v":23},null,{"$f":"C11","v":40},{"$f":"C12","v":"2018-04-10"},{"$f":"C13","v":"1442/10/25"}]
[null,"تاريخ نهاية القرض",{"$f":"TEXT(C12+((C11-1)*29.5),\"B2yyyy/mm/\")&TEXT(C12,\"B2dd\")","v":"1442/10/25"},"التســديدات",{"$f":"SUM(F19:F58)","v":65250},null,"حالة الاشهر وقيمة العقد",null,null,null,"2019-11-01",{"$f":"EDATE(K13,J12)","v":"2023-03-01"}]
[null,null,null,"البـــاقي",{"$f":"-(E13-E11)","v":0},null,"الأشهر المسددة",{"$f":"IF(DATEDIF(K12,NOW(),\"m\")+1>J12,J12,DATEDIF(K12,NOW(),\"m\")+1)","v":40},"المبــالغ المســدة",{"$f":"E13","v":65250},"حـالة العقــد"]
[null,null,"حركة تسديد عميل",null,null,null,"الأشهر المتبقية",{"$f":"J12-H14","v":0},"المبــالغ المتبـقية",{"$f":"E14","v":0},{"$f":"VLOOKUP(C7,المقسطون!B26:N61,11,0)","v":"سداد مبكر"}]
[]
[]
[null,"الشهــر","تاريخ التحصيل","رصيد القرض","القسط","التسديدات"]
[null,{"$f":"IF(ROW()-18>$C$11,\"\",ROW()-18)","v":1},{"$f":"IF(B19=\"\",\"\",TEXT($C$12+((B19-1)*29.5),\"B2yyyy/mm/\")&TEXT($C$12,\"B2dd\"))","v":"1439/07/25"},{"$f":"E11","v":65250},{"$f":"IF(C19=\"\",\"\",IF($E$11=\"\",\"\",$E$11/$C$11))","v":1631.25},{"$f":"IF(B19=\"\",\"\",IF(TODAY()>=h2g(C19),E19,\"\"))","v":1631.25}]
[null,2,{"$f":"IF(B20=\"\",\"\",TEXT($C$12+((B20-1)*29.5),\"B2yyyy/mm/\")&TEXT($C$12,\"B2dd\"))","v":"1439/08/25"},{"$f":"IF(C20=\"\",\"\",D19-E20)","v":63618.75},{"$f":"IF(C20=\"\",\"\",IF($E$11=\"\",\"\",$E$11/$C$11))","v":1631.25},{"$f":"IF(B20=\"\",\"\",IF(TODAY()>=h2g(C20),E20,\"\"))","v":1631.25}]
[null,3,"1439/09/25",{"$f":"IF(C21=\"\",\"\",D20-E21)","v":61987.5},1631.25,{"$f":"IF(B21=\"\",\"\",IF(TODAY()>=h2g(C21),E21,\"\"))","v":1631.25}]
[null,4,"1439/10/25",60356.25,1631.25,{"$f":"IF(B22=\"\",\"\",IF(TODAY()>=h2g(C22),E22,\"\"))","v":1631.25}]
[null,5,"1439/11/25",58725,1631.25,{"$f":"IF(B23=\"\",\"\",IF(TODAY()>=h2g(C23),E23,\"\"))","v":1631.25}]
[null,6,"1439/12/25",57093.75,1631.25,{"$f":"IF(B24=\"\",\"\",IF(TODAY()>=h2g(C24),E24,\"\"))","v":1631.25}]
[null,7,"1440/01/25",55462.5,1631.25,{"$f":"IF(B25=\"\",\"\",IF(TODAY()>=h2g(C25),E25,\"\"))","v":1631.25}]
[null,8,"1440/02/25",53831.25,1631.25,{"$f":"IF(B26=\"\",\"\",IF(TODAY()>=h2g(C26),E26,\"\"))","v":1631.25}]
[null,9,"1440/03/25",52200,1631.25,{"$f":"IF(B27=\"\",\"\",IF(TODAY()>=h2g(C27),E27,\"\"))","v":1631.25}]
[null,10,"1440/04/25",50568.75,1631.25,{"$f":"IF(B28=\"\",\"\",IF(TODAY()>=h2g(C28),E28,\"\"))","v":1631.25}]
[null,11,"1440/05/25",48937.5,1631.25,{"$f":"IF(B29=\"\",\"\",IF(TODAY()>=h2g(C29),E29,\"\"))","v":1631.25}]
[null,12,"1440/06/25",47306.25,1631.25,{"$f":"IF(B30=\"\",\"\",IF(TODAY()>=h2g(C30),E30,\"\"))","v":1631.25}]
[null,13,"1440/07/25",45675,1631.25,{"$f":"IF(B31=\"\",\"\",IF(TODAY()>=h2g(C31),E31,\"\"))","v":1631.25}]
[null,14,"1440/08/25",44043.75,1631.25,{"$f":"IF(B32=\"\",\"\",IF(TODAY()>=h2g(C32),E32,\"\"))","v":1631.25}]
[null,15,"1440/09/25",42412.5,1631.25,{"$f":"IF(B33=\"\",\"\",IF(TODAY()>=h2g(C33),E33,\"\"))","v":1631.25}]
[null,16,"1440/10/25",40781.25,1631.25,{"$f":"IF(B34=\"\",\"\",IF(TODAY()>=h2g(C34),E34,\"\"))","v":1631.25}]
[null,17,"1440/11/25",39150,1631.25,{"$f":"IF(B35=\"\",\"\",IF(TODAY()>=h2g(C35),E35,\"\"))","v":1631.25}]
[null,18,"1440/12/25",37518.75,1631.25,{"$f":"IF(B36=\"\",\"\",IF(TODAY()>=h2g(C36),E36,\"\"))","v":1631.25}]
[null,19,"1441/01/25",35887.5,1631.25,{"$f":"IF(B37=\"\",\"\",IF(TODAY()>=h2g(C37),E37,\"\"))","v":1631.25}]
[null,20,"1441/02/25",34256.25,1631.25,{"$f":"IF(B38=\"\",\"\",IF(TODAY()>=h2g(C38),E38,\"\"))","v":1631.25}]
[null,21,"1441/03/25",32625,1631.25,{"$f":"IF(B39=\"\",\"\",IF(TODAY()>=h2g(C39),E39,\"\"))","v":1631.25}]
[null,22,"1441/04/25",30993.75,1631.25,{"$f":"IF(B40=\"\",\"\",IF(TODAY()>=h2g(C40),E40,\"\"))","v":1631.25}]
[null,23,"1441/05/25",29362.5,1631.25,{"$f":"IF(B41=\"\",\"\",IF(TODAY()>=h2g(C41),E41,\"\"))","v":1631.25}]
[null,24,"1441/06/25",27731.25,1631.25,{"$f":"IF(B42=\"\",\"\",IF(TODAY()>=h2g(C42),E42,\"\"))","v":1631.25}]
[null,25,"1441/07/25",26100,1631.25,{"$f":"IF(B43=\"\",\"\",IF(TODAY()>=h2g(C43),E43,\"\"))","v":1631.25}]
[null,26,"1441/08/25",24468.75,1631.25,{"$f":"IF(B44=\"\",\"\",IF(TODAY()>=h2g(C44),E44,\"\"))","v":1631.25}]
[null,27,"1441/09/25",22837.5,1631.25,{"$f":"IF(B45=\"\",\"\",IF(TODAY()>=h2g(C45),E45,\"\"))","v":1631.25}]
[null,28,"1441/10/25",21206.25,1631.25,{"$f":"IF(B46=\"\",\"\",IF(TODAY()>=h2g(C46),E46,\"\"))","v":1631.25}]
[null,29,"1441/11/25",19575,1631.25,{"$f":"IF(B47=\"\",\"\",IF(TODAY()>=h2g(C47),E47,\"\"))","v":1631.25}]
[null,30,"1441/12/25",17943.75,1631.25,{"$f":"IF(B48=\"\",\"\",IF(TODAY()>=h2g(C48),E48,\"\"))","v":1631.25}]
[null,31,"1442/01/25",16312.5,1631.25,{"$f":"IF(B49=\"\",\"\",IF(TODAY()>=h2g(C49),E49,\"\"))","v":1631.25}]
[null,32,"1442/02/25",14681.25,1631.25,{"$f":"IF(B50=\"\",\"\",IF(TODAY()>=h2g(C50),E50,\"\"))","v":1631.25}]
[null,33,"1442/03/25",13050,1631.25,{"$f":"IF(B51=\"\",\"\",IF(TODAY()>=h2g(C51),E51,\"\"))","v":1631.25}]
[null,34,"1442/04/25",11418.75,1631.25,{"$f":"IF(B52=\"\",\"\",IF(TODAY()>=h2g(C52),E52,\"\"))","v":1631.25}]
[null,35,"1442/05/25",9787.5,1631.25,{"$f":"IF(B53=\"\",\"\",IF(TODAY()>=h2g(C53),E53,\"\"))","v":1631.25}]
[null,36,"1442/06/25",8156.25,1631.25,{"$f":"IF(B54=\"\",\"\",IF(TODAY()>=h2g(C54),E54,\"\"))","v":1631.25}]
[null,37,"1442/07/25",6525,1631.25,{"$f":"IF(B55=\"\",\"\",IF(TODAY()>=h2g(C55),E55,\"\"))","v":1631.25}]
[null,38,"1442/08/25",4893.75,1631.25,{"$f":"IF(B56=\"\",\"\",IF(TODAY()>=h2g(C56),E56,\"\"))","v":1631.25}]
[null,39,"1442/09/25",3262.5,1631.25,{"$f":"IF(B57=\"\",\"\",IF(TODAY()>=h2g(C57),E57,\"\"))","v":1631.25}]
[null,40,"1442/10/25",1631.25,1631.25,{"$f":"IF(B58=\"\",\"\",IF(TODAY()>=h2g(C58),E58,\"\"))","v":1631.25}]