Майкрософт Excel-д антивийн төлбөрийн тооцоо

Зээл авахаасаа өмнө үүнийхээ бүх төлбөрийг тооцоход таатай байх болно. Энэ нь ирээдүйд зээлдэгчийг гэнэтийн хэт их хэмжээтэй болох үед гэнэтийн бэрхшээл, урам хугарахаас аврах болно. Энэ тооцоололд Excel програмууд тусалж чадна. Энэ хөтөлбөрт аннуитет зээлийн төлбөрийг хэрхэн тооцохыг олж мэдье.

Төлбөр тооцоо

Юуны өмнө зээлийн төлбөрийн хоёр хэлбэр байдаг гэдгийг хэлэх ёстой.

• Ялгаатай;
• Аннуитет.

Ялгаатай схемийн дагуу үйлчлүүлэгч зээлийн эх үүсвэр дээр төлбөрийн тэнцүү хувийг сар бүр хүүгийн төлбөр хэлбэрээр хийдэг. Хүүгийн төлбөрийн хэмжээ сар бүр буурч байгаа тул тэдний тооцоолсон зээлийн хэмжээ буурч байна. Тиймээс сарын нийт төлбөр мөн буурдаг.

Анюти схем нь арай өөр хандлагыг ашигладаг. Үйлчлүүлэгч сар бүр зээлийн нийт төлбөр, хүүгийн төлбөрөөс бүрддэг нийт төлбөрийн ижил дүнг хийдэг. Эхлээд зээлийн төлбөрийг зээлийн нийт дүнгээр тооцдог боловч бие буурах тусам хүүгийн хуримтлал буурдаг. Гэхдээ зээлийн нийт төлбөрийн хэмжээ сар бүр нэмэгдсэнтэй холбоотойгоор нийт төлбөрийн хэмжээ өөрчлөгдөөгүй хэвээр байна. Тиймээс цаг хугацаа өнгөрөх тусам нийт сарын төлбөрийн хувь буурч, бие махбодид төлөх хувь хэмжээ нэмэгддэг. Цаашилбал, сарын нийт төлбөр өөрөө зээлийн бүх хугацаанд өөрчлөгддөггүй.

Зүгээр л аннуитетийн төлбөрийг тооцоход бид зогсох болно. Түүнээс гадна одоогийн байдлаар ихэнх банкууд энэ тодорхой схемийг ашигладаг тул энэ нь хамааралтай юм. Энэ тохиолдолд төлбөрийн нийт хэмжээ өөрчлөгдөхгүй, тогтмол хэвээр байгаа тул үйлчлүүлэгчдэд тохиромжтой. Үйлчлүүлэгчид хэр их мөнгө төлөхийг үргэлж мэддэг.

1-р шат: Сарын урьдчилгаа төлбөр тооцоо

Excel-д аннуитетийн схемийг ашиглахдаа сарын шимтгэлийг тооцоход тусгай функц байдаг. PMT санБайна. Энэ нь санхүүгийн операторын ангилалд хамаарна. Энэ функцийн томъёо нь дараахь байдалтай байна.

= PLT (хувь; nper; ps; bs; төрөл)

Таны харж байгаагаар энэ функц нь нэлээд олон тооны аргументуудтай. Үнэн бол тэдгээрийн сүүлийн хоёр нь заавал биш юм.

Аргумент Тендер тодорхой хугацааны хүүгийн хэмжээг заана. Жишээлбэл, жилийн хувь хэмжээг ашигладаг боловч зээлийг сар бүр төлдөг бол жилийн хувь хэмжээг хуваах хэрэгтэй 12 мөн үр дүнг аргумент болгон ашиглах. Хэрэв улирлын төлбөрийн хэлбэрийг ашигладаг бол энэ тохиолдолд жилийн хувь хэмжээг хуваах ёстой 4 гэх мэт.

"Nper" зээлийн эргэн төлөлтийн нийт хугацааг заана. Өөрөөр хэлбэл, зээлийг сарын төлбөртэй нэг жилийн хугацаатай авсан бол хугацааны тоог тооцно 12хэрэв хоёр жилийн турш байвал хугацааны тоо юм 24Байна. Хэрэв зээлийг улирлын төлбөртэй хоёр жилийн хугацаатай авсан бол хугацааны тоо тэнцүү болно 8.

П. тухайн үеийн одоогийн үнэ цэнийг илтгэнэ. Энгийнээр хэлбэл, энэ бол зээлийн эхэн дэх зээлийн нийт хэмжээ, өөрөөр хэлбэл хүү болон бусад нэмэлт төлбөрийг оруулахгүйгээр таны зээлсэн дүн юм.

"Bs" нь ирээдүйн үнэ цэнэ юм. Зээлийн гэрээ дуусах үед зээлийн үндсэн хэсэг болох энэ үнэ цэнэ. Ихэнх тохиолдолд энэ аргумент байдаг "0", зээлийн хугацаа дуусахад зээлдэгч нь зээлдүүлэгчид бүрэн төлөх ёстой. Заасан аргумент нь заавал байх ёстой. Тиймээс, хэрэв буурвал тэгтэй тэнцүү гэж үзнэ.

Аргумент "Төрөл" тооцооллын хугацааг тодорхойлно: хугацааны төгсгөлд эсвэл эхэнд. Эхний тохиолдолд энэ нь үнэ цэнийг шаарддаг "0"хоёр дахь нь - "1"Байна. Ихэнх банкны байгууллагууд тухайн хугацааны эцэст төлбөр хийхдээ яг тохирох хувилбарыг ашигладаг. Энэ аргумент нь бас сонголттой бөгөөд хэрэв орхигдуулсан бол тэг гэж үзнэ.

Одоо PMT функцийг ашиглан сарын урьдчилгаа төлбөрийг тооцоолох тодорхой жишээ рүү шилжих цаг болжээ. Тооцооллын хувьд бид зээлийн хүүний хэмжээг заасан эх сурвалжтай хүснэгтийг ашиглана.12%), зээлийн хэмжээ (500,000 рубль) болон зээлийн хугацаа (24 сар) Түүнээс гадна төлбөрийг хугацаа бүрийн эцэст сар бүр хийдэг.

1. Тооцооллын үр дүнг харуулах хуудсан дээрх элементийг сонгоод дүрс дээр дарна уу "Функц оруулах"томъёоны талбайн дэргэд байрлуулсан.



2. Цонхыг асааж байна. Функц шидтэнүүдБайна. Ангилалд нь "Санхүүгийн" нэрийг сонгоно уу "PLT" ба товчийг дарна уу "ОК".



3. Үүний дараа операторын аргументын цонх нээгдэнэ. PMT сан.

   Талбай дээр Тендер хугацаанд хувийг оруулна уу. Үүнийг гараар хийж болно, зүгээр л хувь хэмжээг тогтоож болно, гэхдээ бид үүнийг хуудсан дээрх тусдаа нүдэнд зааж өгсөн тул түүнд холбоос өгөх болно. Бид курсорыг талбарт байрлуулж, дараа нь тохирох нүдэн дээр дарна уу. Гэхдээ бидний санаж байгаагаар бидний хүснэгтэд жилийн хүүгийн хэмжээг тогтоож, төлбөрийн хугацаа сартай тэнцдэг. Тиймээс, бид жилийн хувь хэмжээ, эсвэл өөрөөр хэлбэл түүний дотор байгаа эсийн холбоосыг тоонд хуваадаг 12тухайн жилийн сарын тоонд харгалзана. Хэсэг нь аргументын цонхны талбарт шууд хийгддэг.

   Талбай дээр "Nper" зээлийн хугацааг тогтоосон. Тэр бидэнтэй тэнцүү 24 сарын турш Та талбарт дугаар оруулах боломжтой 24 гараар хийсэн боловч бид өмнөх тохиолдолд адилаар энэ хүснэгтийн байршлыг анхны хүснэгтэд байрлуулах холбоосыг зааж өгсөн болно.

   Талбай дээр П. Зээлийн анхны дүнг заана. Тэр тэнцүү 500,000 рубльБайна. Өмнөх тохиолдлуудын адилаар бид энэ заалт орсон хуудасны элементийн холбоосыг зааж өгнө.

   Талбай дээр "Bs" бүрэн төлбөр хийсний дараа зээлийн дүнг заана. Бидний санаж байгаагаар энэ утга бараг үргэлж тэг байдаг. Энэ талбарт дугаараа тохируулна уу "0"Байна. Хэдийгээр энэ аргументыг бүхэлд нь орхиж болно.

   Талбай дээр "Төрөл" сарын эхэнд эсвэл төгсгөлд төлбөрийг хийж болохыг анхаарна уу. Энд, ихэнх тохиолдолд, сарын эцэст үйлдвэрлэдэг. Тиймээс тоог нь тогтооно уу "0"Байна. Өмнөх аргументын нэгэн адил та энэ талбарт юу ч оруулах боломжгүй тул програм нь тэгтэй тэнцэх утгыг агуулна гэж үзнэ.

   Бүх өгөгдөл оруулсны дараа товчлуур дээр дарна уу "ОК".



4. Үүний дараа тооцооллын үр дүнг энэ гарын авлагын эхний догол мөрөнд онцолсон нүдэнд харуулав. Таны харж байгаагаар сарын нийт зээлийн төлбөрийн хэмжээ юм 23536.74 рубль байнаБайна. Энэ дүнг урд нь "-" тэмдэгээр андуурч болохгүй. Тиймээс Excel нь энэ нь мөнгөний зардал, алдагдал гэдгийг харуулж байна.



5. Зээлийн нийт хугацааны төлбөрийн нийт дүнг зээлийн эх үүсвэрийн эргэн төлөлт, сарын хүүг харгалзан тооцохын тулд тухайн сарын төлбөрийн дүнг үржүүлэхэд хангалттай.23536.74 рубль байна) сарын тоогоор (24 сар) Таны харж байгаагаар манай зээлийн хугацааны нийт төлбөрийн нийт дүн байв 564881.67 рубль.



6. Одоо та зээлийн илүү төлөлтийн хэмжээг тооцоолж болно. Үүнийг хийхийн тулд зээлийн нийт төлбөрийн дүнгээс хүү, зээлийн байгууллага, авсан анхны дүнгээс хасна. Гэхдээ эдгээр үнэт зүйлсийн эхнийх нь аль хэдийн гарын үсэг зурсан гэдгийг бид санаж байна "-"Байна. Тиймээс, бидний тодорхой тохиолдолд тэдгээрийг нугалах шаардлагатай болж хувирдаг. Таны харж байгаагаар нийт хугацааны зээлийн нийт төлбөр төлсөн байна 64 881.67 рубль байна.

Хичээл: Excel-ийн онцлог шинж чанар

2-р шат: төлбөрийн дэлгэрэнгүй мэдээлэл

Одоо, Excel-ийн бусад операторын тусламжтайгаар бид тухайн сард зээл дээр хэдэн төгрөг төлсөн, хүү нь хэр зэрэг төлж байгааг харахын тулд төлбөрийн дэлгэрэнгүй мэдээллийг сар бүр гаргаж өгдөг. Эдгээр зорилгоор бид Excel-д өгөгдөл оруулах хүснэгтийг зурдаг. Энэ хүснэгтийн мөрүүд нь холбогдох хугацаа, сартай тохирч байх болно. Бидэнтэй хамт зээлийн хугацааг харгалзан үзнэ 24 сарууд, дараа нь эгнээний тоо бас тохирох болно. Багана нь зээлийн эх үүсвэрийн төлбөр, хүүгийн төлбөр, сарын төлбөр, өмнөх хоёр баганын нийлбэр, түүнчлэн төлөх ёстой үлдсэн дүнг харуулна.

1. Зээлийн байгууллагаас төлбөрийн хэмжээг тодорхойлохын тулд функцийг ашиглана уу OSPLT, эдгээр нь зөвхөн эдгээр зорилгод зориулагдсан. Мөрөнд байгаа нүдэнд курсорыг тавь "1" ба багананд "Зээлийн биед төлөх төлбөр"Байна. Товчин дээр дарна уу "Функц оруулах".



2. Явна уу Онцлог шидтэнБайна. Ангилалд нь "Санхүүгийн" нэрийг нь тэмдэглээрэй OSPLT товчийг дар "ОК".



3. OSPLT операторын аргументын цонх эхэлнэ. Энэ нь дараах синтакс агуулгатай байна.

   = OSPLT (Бооцоо; Хугацаа; Nper; Ps; BS)

   Таны харж байгаагаар энэ функцийн аргументууд нь операторын аргументтой бараг л давхцаж байна PMT сан, зөвхөн нэмэлт аргументын оронд "Төрөл" шаардлагатай аргумент нэмсэн "Хугацаа"Байна. Энэ нь төлбөрийн хугацааны тоог, бидний тодорхой тохиолдолд сарны дугаарыг заана.

   Функц аргументын цонхны аль хэдийн танигдсан талбаруудыг бид бөглөнө OSPLT функцэд ашигласан ижил өгөгдөл PMT санБайна. Ирээдүйд томъёог дүүргэлтийн тэмдэг ашиглан хуулж авах болно гэдгийг харгалзан үзэхийн тулд та талбар дахь бүх линкийг абсолют болгох хэрэгтэй бөгөөд ингэснээр тэдгээр нь өөрчлөгдөхгүй. Үүнийг хийхийн тулд координатын утга бүрийн босоо болон хэвтээ чиглэлд долларын тэмдэг тавь. Гэхдээ үүнийг координатыг тодруулж, функцийн товчлуурыг дарахад л илүү хялбар болно F4Байна. Долларын тэмдгийг автоматаар зөв газруудад байрлуулна. Түүнчлэн жилийн ханшийг хуваах ёстойг бүү мартаарай 12.



4. Гэхдээ функцэд байхгүй өөр нэг шинэ аргумент бидэнд байна. PMT санБайна. Энэ аргумент "Хугацаа"Байна. Харгалзах талбарт холбоосыг баганын эхний нүдэнд тохируулна уу "Хугацаа"Байна. Энэ шийт элемент нь тоо агуулж байна "1", энэ нь зээлийн эхний сарын тоог заана. Гэхдээ өмнөх талбаруудаас ялгаатай нь заасан талбарт бид холбоосыг харьцангуй үлдээж, үүнийг үнэмлэхүй болгож болохгүй.

   Дээр дурдсан бүх өгөгдлийг оруулсны дараа товчин дээр дарна уу "ОК".



5. Үүний дараа бидний урьд нь хуваарилсан нүдэнд эхний сарын зээлийн дүнгийн дүнг харуулна. Тэр хийх болно 18,536.74 рубль байна.



6. Дараа нь дээр дурдсанчлан бид энэ томъёог бөглөх тэмдэглэгээг ашиглан баганын үлдсэн нүднүүдэд хуулах хэрэгтэй. Үүнийг хийхийн тулд курсорыг томъёо агуулсан нүдний баруун доод буланд байрлуулна уу. Курсорыг загалмай болгон хувиргадаг бөгөөд үүнийг бөглөх тэмдэг гэж нэрлэдэг. Зүүн хулганы товчийг дараад хүснэгтийн төгсгөлд чирнэ үү.



7. Үүний үр дүнд баганын бүх нүд дүүрсэн байна. Одоо бид сар бүр зээлийн эргэн төлөлт хийх хуваарьтай байгаа. Дээр дурдсанчлан, энэ зүйлд заасны дагуу төлбөрийн хэмжээ шинэ үе үе нэмэгдэх болно.



8. Одоо бид сар бүр хүүгийн төлбөрийн тооцоог хийх хэрэгтэй. Эдгээр зорилгоор бид операторыг ашиглах болно PRPLTБайна. Багана доторх эхний хоосон нүдийг сонгоно уу Хүүгийн төлбөрБайна. Товчин дээр дарна уу "Функц оруулах".



9. Эхлэх цонхонд Функц шидтэнүүд ангилалд "Санхүүгийн" бид сонголтоо хийдэг PRPLTБайна. Товчин дээр дарна уу. "ОК".



10. Функцийн аргументын цонх эхэлнэ. PRPLTБайна. Үүний синтакс нь дараах байдалтай байна.

    = PRPLT (Бооцоо; Хугацаа; Nper; Ps; BS)

    Таны харж байгаагаар энэ функцийн аргументууд нь операторын ижил төстэй элементүүдтэй яг ижил байна OSPLTБайна. Тиймээс бид өмнөх аргумент цонхонд оруулсан өгөгдлийг ижил өгөгдлийг оруулна. Талбай дээрх холбоосыг бид нэгэн зэрэг мартаж болохгүй "Хугацаа" харьцангуй байх ёстой бөгөөд бусад бүх талбарт координатыг үнэмлэхүй хэлбэр болгон бууруулах ёстой. Үүний дараа товчлуур дээр дарна уу "ОК".



11. Дараа нь эхний сарын зээлийн хүүгийн хэмжээг тооцоолсны үр дүнг зохих хайрцагт харуулав.



12. Дүүргэлт тэмдэглэгээг ашиглан бид томъёог баганын үлдсэн элементүүд рүү хуулж, улмаар зээлийн хүүгийн сарын төлбөрийн хуваарийг гаргаж өгдөг. Өмнө дурьдсанчлан, сар бүрээс энэ төрлийн төлбөрийн үнэ буурдаг.



13. Одоо бид нийт сарын төлбөрийг тооцох ёстой. Энэхүү тооцооллын хувьд та энгийн арифметик томъёог ашиглаж болох тул ямар ч оператор руу хандах ёсгүй. Эхний баганын нүднүүдийн агуулгыг нэмнэ "Зээлийн биед төлөх төлбөр" ба Хүүгийн төлбөрБайна. Үүнийг хийхийн тулд тэмдгийг тавь "=" баганын эхний хоосон нүд рүү "Сарын нийт төлбөр"Байна. Дараа нь бид дээрх хоёр элемент дээр товшоод тэдгээрийн хооронд тэмдэг тавина "+"Байна. Товч дээр дарна уу Оруулна уу.



14. Дараа нь өмнөх тохиолдлуудын адилаар дүүргэлтийн тэмдэглэгээг ашиглан баганыг өгөгдөлөөр дүүргэнэ. Таны харж байгаагаар гэрээний хугацаанд нийт зээлийн төлбөр, зээлийн байгууллага, хүү зэргийг багтаасан болно 23536.74 рубль байнаБайна. Үнэндээ бид энэ үзүүлэлтийг ашиглан тооцоолсон PMT санБайна. Гэхдээ энэ тохиолдолд энэ нь зээлийн байгууллага, хүүгийн төлбөр, төлбөрийн хэмжээнээс илүү нарийвчлан харуулсан болно.



15. Одоо та өгөгдлийг баганад нэмэх хэрэгтэй бөгөөд үүнийг сар бүр төлөх шаардлагатай байгаа зээлийн дүнгийн үлдэгдлийг харуулах болно. Баганы эхний нүдэнд "Төлбөрийн үлдэгдэл" тооцоо хамгийн хялбар байх болно. Бид хүснэгтэд заасан анхны зээлийн дүн, зээлийн эхний төлбөрийг тооцооны хүснэгтээс хасах хэрэгтэй. Гэхдээ, тоонуудын аль нэгэнд нь бид аль хэдийн тэмдэгтэй байгааг харгалзан үзэх хэрэгтэй "-", дараа нь тэдгээрийг авч болохгүй, харин нугалав. Бид үүнийг хийж, товчлуур дээр дарна уу Оруулна уу.



16. Гэхдээ хоёр дахь болон дараагийн сарын дараа төлөх үлдэгдлийг тооцоолох нь арай илүү төвөгтэй байх болно. Үүнийг хийхийн тулд зээлийн эхэнд зээлийн байгууллагаас өмнөх үеийн төлбөрийн нийт дүнгээс хасах шаардлагатай байна. Тэмдгийг тавь "=" баганын хоёр дахь нүдэнд "Төлбөрийн үлдэгдэл"Байна. Дараа нь, зээлийн үндсэн дүн орсон нүдний холбоосыг зааж өгнө. Түлхүүрийг тодруулж, дарж үүнийг үнэмлэхүй болго F4Байна. Дараа нь бид тэмдэг тавьдаг "+", манай тохиолдолд хоёр дахь утга сөрөг байх болно. Үүний дараа товчлуур дээр дарна уу "Функц оруулах".



17. Эхлэлээ Онцлог шидтэнангилалд шилжих хэрэгтэй "Математик"Байна. Тэнд бид бичээсийг онцолсон СУМ ба товчийг дарна уу "ОК".



18. Функцийн аргумент цонх эхэлнэ СУМБайна. Заасан оператор нь бид багананд хийх шаардлагатай нүднүүдийн өгөгдлийг нэгтгэхэд үйлчилдэг "Зээлийн биед төлөх төлбөр"Байна. Энэ нь дараах синтакс агуулгатай байна.

    = SUM (тоо1; дугаар2; ...)

    Аргументы нь тоо агуулсан нүднүүдийн лавлагаа юм. Бид курсорыг талбарт тохируулсан "Дугаар1"Байна. Дараа нь бид хулганы зүүн товчийг дараад хуудсан дээрх баганын эхний хоёр нүдийг сонгоно "Зээлийн биед төлөх төлбөр"Байна. Талбар дээр бидний харж байгаагаар мужийн холбоос гарч ирнэ. Энэ нь бүдүүн гэдсээр тусгаарлагдсан хоёр хэсгээс бүрдэнэ: мужийн эхний нүд ба сүүлчийн холбоос. Ирээдүйд бөглөх тэмдэглэгээг ашиглан заасан томъёог хуулах боломжтой байхын тулд бид холбоосын эхний хэсгийг муж руу үнэмлэхүй болгож байна. Үүнийг сонгоод функцийн товчлуур дээр дарна уу F4Байна. Холболтын хоёр дахь хэсэг харьцангуй хэвээр байна. Одоо дүүргэлтийн тэмдгийг ашиглахдаа мужийн эхний нүд бэхлэгдэх бөгөөд сүүлчийнх нь доошоо урагш сунах болно. Энэ бол зорилгоо биелүүлэхэд хэрэгтэй зүйл юм. Дараа нь товчин дээр дарна уу "ОК".



19. Тиймээс, хоёр дахь сарын дараа зээлийн өрийн үлдэгдлийн үр дүнг нүдэнд харуулав. Одоо энэ нүднээс эхлэн бид томъёоны тэмдэглэгээг ашиглан томъёог хоосон баганын элементүүдэд хуулж авна.



20. Зээлийн нийт хугацааны зээлийн үлдэгдлийн сарын тооцоо. Хүлээгдэж буйгаар хугацааны эцэст энэ дүн тэг болно.

Ийнхүү бид зээлийнхээ төлбөрийг тооцож үзээд зогсохгүй зээлийн тооцооны төрлийн ажлыг зохион байгууллаа. Анюти схем дээр ажиллах болно. Хэрэв бид анхны хүснэгтэд жишээлбэл зээлийн хэмжээ болон жилийн хүүгийн хэмжээг өөрчилбөл эцсийн хүснэгтэд өгөгдлийг автоматаар тооцоолох болно.Тиймээс, энэ нь зөвхөн тодорхой тохиолдлын хувьд ганц удаа ашиглагдаагүй, харин янз бүрийн нөхцөлд, жилийн хугацаатай, аннуитетийн схемийн дагуу зээлийн сонголтыг тооцоолоход хэрэглэгддэг.

Хичээл: Excel дэх санхүүгийн чиг үүрэг

Таны харж байгаагаар гэртээ Excel програмыг ашигласнаар эдгээр зорилгоор операторыг ашиглан жилийн зээлийн нийт төлбөрийг жилийн анкетын дагуу тооцоолох боломжтой. PMT санБайна. Үүнээс гадна функцийг ашиглах OSPLT ба PRPLT та зээлийн биен дэх төлбөрийн хэмжээг, заасан хугацаанд хүүг тооцоолж болно. Энэхүү бүх ачаа тээшний үүргүүдийг хамт хэрэгжүүлснээр жилийн төлбөрийн төлбөрийг тооцоолоход нэгээс олон удаа ашиглагдах боломжтой зээлийн хүчирхэг тооцоолуур хийх боломжтой болно.