Lissafin kuɗi na shekara-shekara a cikin Microsoft Excel

Kafin ɗaukar lamuni, zai yi kyau idan aka lissafa duk kuɗin da ake bin sa. Wannan zai ceci mai aro a nan gaba daga matsaloli da rashin tsammani daban-daban da suka faru yayin da ya nuna cewa biyan bashin ya yi yawa. Kayan aikin Excel na iya taimakawa tare da wannan lissafin. Bari mu gano yadda ake lissafin biyan kuɗin fito na shekara a cikin wannan shirin.

Calcuididdigar biyan kuɗi

Da farko dai, dole ne a faɗi cewa akwai nau'ikan biyan rancen guda biyu:

• Bambanta;
• Annuity.

A cikin tsarin da aka bambance, abokin ciniki yana yin daidai adadin biya akan jikin lamunin da aka biya da kuma biyan biyan bashin wata-wata zuwa banki. Adadin biyan kuɗi yana raguwa kowane wata, kamar yadda jikin lamunin daga wanda aka lissafta shi ke raguwa. Don haka, ana biyan jimlar kowane wata.

Tsarin tsarin shekara yana amfani da hanyar dabam. Abokin ciniki kowane wata yana yin daidai adadin adadin biyan, wanda ya ƙunshi biyan kuɗi akan jikin lamuni da biyan bashin sha'awa. A farko, ana lasafta biyan bashin ne domin duk adadin rance, amma kamar yadda jiki ke raguwa, ribar tarawa ke raguwa. Amma jimlar adadin biyan bashin ba ta canzawa ba saboda karuwar kowane wata a yawan biya a jikin mai rance. Don haka, a kan lokaci, kashi na sha'awa cikin jimlar biyan kowane wata yana raguwa, kuma adadin biya ta jiki yana ƙaruwa. Haka kuma, jimlar biyan kowane wata da kanta ba ta canzawa a duk tsawon lokacin aro.

Kawai akan lissafin biyan kudin shekara, za mu dakatar. Haka kuma, wannan ya dace, tunda a yanzu yawancin bankuna suna amfani da wannan tsarin. Ya dace da abokan ciniki, saboda a wannan yanayin jimlar biyan kuɗi baya canzawa, kasancewar ta kasance ajali. Abokan ciniki koyaushe suna san nawa zasu biya.

Mataki na 1: lissafin biya na wata-wata

Don ƙididdige gudummawar kowane wata lokacin amfani da tsarin ƙimar kuɗi a cikin Excel akwai aiki na musamman - PMT. Ya kasance ga rukuni na manajan kuɗi. Dabarar wannan aikin kamar haka:

= PLT (kudi; nper; ps; bs; nau'in)

Kamar yadda kake gani, wannan aikin yana da adadin ɗaruruwan maganganu na adalci. Gaskiya ne, ƙarshen su biyun ba na tilas bane.

Hujja Biya yana nuna ƙimar sha'awa na wani zamani. Idan, alal misali, ana amfani da kudin shekara-shekara, amma ana biyan bashin kowane wata, to ya kamata a raba ragin shekara-shekara ta 12 kuma amfani da sakamako azaman hujja. Idan aka yi amfani da nau'in biyan kashi ɗaya na kwata, to a wannan yanayin ya kamata a raba ragin shekara-shekara ta 4 da sauransu

"Muryar ƙasa" yana nuna adadin lokacin biyan bashin. Wato, idan aka ɗauki lamuni na shekara ɗaya tare da biyan wata-wata, to, ana la'akari da adadin adadin lokaci 12idan tsawon shekaru biyu, to adadin kwanakin yana 24. Idan an ɗauki lamunin shekaru biyu tare da biyan kuɗin kwata, to, adadin adadin daidai yake 8.

Zab yana nuna darajar yanzu a yanzu. A cikin kalmomi masu sauƙi, wannan shine jimlar adadin aro a farkon lokacin aro, shine, adadin da kuka aro, ban da sha’awa da sauran ƙarin biya.

"Bs" shine darajar nan gaba. Wannan darajar, wanda zai zama jiki na lokacin rance a lokacin kammala yarjejeniyar bada lamuni. A mafi yawan lokuta, wannan mahawara ita ce "0", tunda mai bin bashi a ƙarshen lokacin bayar da bashi dole ne ya biya mai ba da bashi cikakke. Takaddun hujja ba na tilas bane. Don haka, idan ya fadi, to ana ɗaukar daidai yake da sifili.

Hujja "Nau'in" yana tantance lokacin lissafin: a ƙarshen ko a farkon lokacin. A farkon magana, yana ɗaukar ƙimar "0"kuma a na biyu - "1". Yawancin cibiyoyin banki suna amfani da zaɓi daidai tare da biyan kuɗi a ƙarshen zamani. Wannan mahawara ma tilas ce, kuma idan ta tsallake ana ganin ta zama ba komai.

Yanzu lokaci ya yi da za mu matsa zuwa wani takamaiman misali na yin lissafin abin biya na wata-wata ta amfani da aikin PMT. Don ƙididdigar, muna amfani da teburin tare da bayanan asalin, inda aka nuna ƙimar sha'awa akan lamuni (12%), adadin aro (500,000 rubles) da lokacin bada aro (Watanni 24) Haka kuma, ana biyan kuɗi kowane wata a ƙarshen kowane lokaci.

1. Zaɓi kashi a takardar wanda za a nuna sakamakon lissafin, kuma danna kan gunkin "Saka aikin"an sanya shi kusa da dabarar dabara.



2. An bude taga. Wizards na Aiki. A cikin rukuni "Kudi" zaɓi sunan "PLT" kuma danna maballin "Ok".



3. Bayan wannan, taga muhawara mai aiki yana buɗewa. PMT.

   A fagen Biya shigar da kashi na tsawon lokacin. Ana iya yin wannan da hannu, kawai ta saita sau, amma muna nuna shi a cikin sel daban akan takarda, saboda haka za mu ba da hanyar haɗi zuwa gare ta. Mun sanya siginan kwamfuta a cikin filin, sannan danna kan tantanin da ya dace. Amma, kamar yadda muke tunawa, a cikin teburinmu an saita rarar kuɗi na shekara-shekara, kuma lokacin biyan kuɗi daidai yake da wata ɗaya. Sabili da haka, mun rarraba ƙimar shekara-shekara, ko kuma hanyar haɗi zuwa tantanin da yake a ciki, zuwa lambar 12m ga yawan watanni a shekara. Ana yin rabo kai tsaye a cikin filin muhawara.

   A fagen "Muryar ƙasa" an tsara lokacin bayar da lamuni. Yana daidai da mu 24 tsawon watanni. Kuna iya shigar da lamba a cikin filin 24 da hannu, amma mu, kamar yadda ya gabata, muna nuna hanyar haɗi zuwa wurin wannan alamar a cikin teburin farko.

   A fagen Zab An nuna adadin aro na farko. Ita daidai take 500,000 rubles. Kamar yadda ya gabata, muna nuna hanyar haɗi zuwa ga takardar abin da aka ƙunsa wannan abin nuna.

   A fagen "Bs" yana nuna adadin bashin, bayan cikakken biyan. Kamar yadda muke tunawa, wannan darajar kusan kullum ba komai bane. Saita lamba a wannan filin "0". Kodayake za a iya kawar da wannan mahaɗan gaba ɗaya.

   A fagen "Nau'in" nuna a farkon ko a ƙarshen watan ana biyan kuɗi. Anan, kamar yadda a mafi yawan lokuta, ana yin sa a ƙarshen watan. Saboda haka, saita lamba "0". Kamar yadda yake a cikin magana ta baya, ba za ku iya shigar da komai a cikin wannan filin ba, to shirin zai ɗauka ta hanyar tsohuwar cewa ya ƙunshi ƙimar daidai da sifili.

   Bayan an shigar da dukkan bayanan, danna maɓallin "Ok".



4. Bayan wannan, sakamakon lissafin yana nunawa cikin tantanin halitta wanda muka haskaka a sakin farko na wannan littafin. Kamar yadda kake gani, adadin adadin kuɗi na kowane wata shine 23536.74 rubles. Kada ku rikita batun alamar “-” a gaban wannan adadin. Don haka, Excel ta nuna cewa wannan kashe kashe ne, watau asara ne.



5. Don yin lissafin jimlar adadin biyan bashin na dukkan rance na rance, la’akari da biyan bashin da ke rance da kuma biyan bashin wata, ya ishe ku ninka yawan abin biya na wata-wata (23536.74 rubles) da yawan watanni (Watanni 24) Kamar yadda kake gani, jimlar biyan dukkan alamu na aro a lamarinmu ya kasance 564881.67 rubles.



6. Yanzu zaku iya lissafa adadin biyan bashin akan rancen. Don yin wannan, cire kuɗi daga jimlar biya akan rance, gami da sha'awa da kuma rancen, adadin farko da aka aro. Amma mun tuna cewa an riga an sanya hannu kan farkon waɗannan ƙimar "-". Sabili da haka, a cikin yanayinmu na musamman, ya juya cewa suna buƙatar ninkawa. Kamar yadda kake gani, jimlar biyan bashi domin duk lokacin ya kasance 64 881.67 rubles.

Darasi: Mayan Maɗaukaki

Mataki na 2: cikakkun bayanai

Yanzu kuma, tare da taimakon sauran manajan Excel, za mu yi cikakken bayani na wata-wata don ganin nawa muke biya akan rance a cikin wata takamaiman, kuma nawa ne riba. Don waɗannan dalilai, mun zana a Excel tebur da zamu cika da bayanai. Layuka a cikin wannan tebur zasu dace da lokacin da ya dace, wato, zuwa watan. Ganin cewa lokacin bada lamuni tare da mu shine 24 watanni, to yawan layuka ma zasu dace. Lissafin suna nuni da biyan jikin kungiyar aro, biyan bashin banki, jimlar biyan wata-wata, wanda shine jimlar kaso biyu da suka gabata, da kuma sauran kudinda aka biya.

1. Don ƙididdige adadin biya da jikin lamuni, yi amfani da aikin OSPLT, wanda kawai aka tsara don waɗannan dalilai. Saita siginan kwamfuta zuwa wayar da ke kan layi "1" kuma a cikin shafi "Sakamako akan jikin aro". Latsa maballin "Saka aikin".



2. Je zuwa Mayan fasalin. A cikin rukuni "Kudi" alama sunan OSPLT kuma latsa maɓallin "Ok".



3. Ana fara bayyana mahawara mai ƙirar OSPLT. Ya na da Syntax mai zuwa:

   = OSPLT (Bet; Lokaci; Nper; Ps; BS)

   Kamar yadda kake gani, muhawara ta wannan aikin kusan ta zo daidai da muhawara mai aiki PMT, kawai maimakon hujja mai zaɓi "Nau'in" an kara bayani "Lokaci". Yana nuna adadin lokacin biya, kuma a cikin yanayinmu, yawan watan.

   Mun cika wuraren da muka riga muka saba da taga taga aiki na aiki OSPLT iri ɗaya bayanan da aka yi amfani da shi don aikin PMT. Ba kawai za a iya tabbatar da cewa a nan gaba za a kwafa fos ɗin ta amfani da alamar cikawa ba, kuna buƙatar sanya duk hanyoyin haɗin cikin filayen don kada su canza. Don yin wannan, sanya alamar dala a gaban kowane darajar daidaitawa tsaye da kwance. Amma yana da sauƙi a yi wannan ta hanyar ba da alama da daidaitawa da danna maɓallin aikin F4. Za a sanya alamar dala a wuraren da suka dace ta atomatik. Hakanan kar ku manta cewa ya kamata a raba ragin shekara-shekara ta 12.



4. Amma muna da ƙarin sabbin hujja waɗanda aikin ba su da shi. PMT. Wannan magana "Lokaci". A cikin filin mai dacewa, saita hanyar haɗi zuwa allon farko na shafi "Lokaci". Wannan samfurin yana dauke da lamba "1", wanda ke nuna adadin watan farko na bada rance. Amma sabanin filayen da suka gabata, a filin da aka ƙayyade mun bar dangin mahaɗin, kuma kada ku sanya shi cikakken.

   Bayan duk bayanan da muka ambata a sama sun shigar, danna kan maɓallin "Ok".



5. Bayan wannan, a cikin tantanin da muka sanya a baya, za a nuna adadin biya akan jikin lamuni na farko ga watan. Za ta yi 18,536.74 rubles.



6. Bayan haka, kamar yadda aka ambata a sama, ya kamata mu kwafa wannan dabara ga sauran sel waɗanda ke ta amfani da alamar alamar. Don yin wannan, saita siginan kwamfuta a cikin ƙananan kusurwar dama na tantanin halitta wanda ya ƙunshi tsari. Mayar da siginar an canza shi zuwa gicciye, wanda ake kira alamar alamar. Riƙe maɓallin linzamin kwamfuta na hagu kuma ja shi zuwa ƙarshen tebur.



7. Sakamakon haka, duk ƙwayoyin da ke cikin shafi sun cika. Yanzu muna da jadawalin biyan bashin kowane wata. Kamar yadda aka ambata a sama, adadin biyan kuɗi a ƙarƙashin wannan labarin yana ƙaruwa tare da kowane sabon lokacin.



8. Yanzu muna buƙatar yin lissafin kowane wata na biyan kuɗi. Don waɗannan dalilai za mu yi amfani da mai aiki KYAUTA. Zaɓi wayar farko da aka ɓoye a cikin shafi Biyan Sha’awa. Latsa maballin "Saka aikin".



9. A cikin farawa taga Wizards na Aiki a cikin rukuni "Kudi" muna yin zabi KYAUTA. Latsa maballin. "Ok".



10. Farashin muhawara na aiki fara. KYAUTA. Syntax kamar haka:

    = PRPLT (Bet; Lokaci; Nper; Ps; BS)

    Kamar yadda kake gani, muhawara ta wannan aikin daidai take da abubuwan ɗaya kwatankwacin mai aiki OSPLT. Sabili da haka, muna kawai shigar da bayanan guda ɗaya a cikin taga wanda muka shiga a cikin taga gardamar da ta gabata. Ba mu manta a lokaci guda cewa mahaɗin a fagen ba "Lokaci" dole ne ya zama dangi, kuma a duk sauran fannoni dole ne a rage haɗin gwiwar zuwa cikakken tsari. Bayan haka, danna maɓallin "Ok".



11. Sannan, sakamakon ƙididdigar yawan biyan kuɗi na sha'anin rance na farkon watan da aka nuna a cikin akwatin da ya dace.



12. Yin amfani da alamar cikawa, za mu kwafa dabarar cikin sauran abubuwan da ke cikin shafin, ta haka muna samun jadawalin biyan kuɗi na wata-wata don sha'awa kan aro. Kamar yadda muke gani, kamar yadda aka fada a baya, daga wata zuwa wata darajar wannan nau'in biyan yana raguwa.



13. Yanzu dole ne mu lissafta jimlar biyan kowane wata. Don wannan ƙididdigar, bai kamata ku nemi mafaka ga kowane ma'aikaci ba, tunda zaku iya amfani da tsari mai sauƙi na lissafi. Theara abubuwan da ke cikin ƙwayoyin sel na farkon watan ginshiƙai "Sakamako akan jikin aro" da Biyan Sha’awa. Don yin wannan, saita alamar "=" zuwa farkon komai kwayar halitta "Jimlar albashi na wata-wata". Sannan mun danna abubuwan biyu da ke sama, suna sanya alama a tsakaninsu "+". Danna maballin Shigar.



14. Na gaba, ta amfani da alamar cikawa, kamar yadda a lokutan baya, cika allon da bayanai. Kamar yadda kake gani, tsawon tsawon lokacin kwangilar, jimlar biyan kowane wata, gami da biyan kuɗi ta hannun mai rance da riba, zasu kasance 23536.74 rubles. A gaskiya, mun rigaya lasafta wannan alamar ta amfani PMT. Amma a wannan yanayin an gabatar da shi sosai, daidai a matsayin adadin biyan kuɗi akan jikin lamuni da kuma sha'awa.



15. Yanzu kuna buƙatar ƙara bayanai a cikin shafi, wanda zai nuna kowane wata ma'auni na adadin aro wanda har yanzu yana buƙatar biya. A cikin tantanin farko na shafi "Bayar da damar biya" lissafin zai zama mafi sauki. Muna buƙatar cire daga adadin farko na aro, wanda aka nuna a cikin tebur tare da bayanan farko, biyan bashin da aka biya don farkon watan a teburin lissafin. Amma, ba da gaskiyar cewa ɗayan lambobin mun riga muna da wata alama "-", to bai kamata a kwashe su ba, amma ana jera su. Muna yin wannan kuma danna maballin Shigar.



16. Amma yin lissafin ma'auni saboda bayan wata na biyu da na gaba zai zama da ɗan rikitarwa. Don yin wannan, muna buƙatar rage daga jikin ara a farkon lokacin aro jimlar biyan kuɗi akan jikin lamunin don lokacin da ya gabata. Saita alamar "=" a cikin sel na biyu na shafi "Bayar da damar biya". Na gaba, muna nuna hanyar haɗi zuwa tantanin, wanda ya ƙunshi adadin aro na farko. Sanya shi cikakke ta hanyar sa alama da latsa madannin F4. Sannan mun sanya alama "+", tunda darajar ta biyu a yanayinmu zata zama mara kyau. Bayan haka, danna maɓallin "Saka aikin".



17. Ya fara Mayan fasalina cikin abin da kuke buƙatar matsawa zuwa rukunin "Ilmin lissafi". A nan muna nuna alamar SAURARA kuma danna maballin "Ok".



18. Farashin muhawara na aiki yana farawa SAURARA. Ma'aikacin da aka ƙayyade yana ba da sabis don taƙaita bayanai a cikin sel, wanda muke buƙatar aiwatarwa a cikin shafi "Sakamako akan jikin aro". Ya na da Syntax mai zuwa:

    = SUM (lamba1; lamba2; ...)

    Jayayyar ita ce nassi ga sel waɗanda ke ɗauke da lambobi. Mun sanya siginan kwamfuta zuwa filin "Lambar1". Sannan mun riƙe maɓallin linzamin kwamfuta na hagu kuma zaɓi farkon sel biyu na layin a kan takardar "Sakamako akan jikin aro". A fagen, kamar yadda muke gani, an nuna hanyar haɗi zuwa kewayon. Ya ƙunshi bangarori biyu, abubuwa biyu da yake wakilta: haɗin zuwa sel na farko na kewayon da na ƙarshe. Domin samun damar yin amfani da samfurin da aka ƙayyade ta amfani da alamar cika a nan gaba, sai mu sanya ɓangaren farko na haɗin yanar gizo zuwa cikakke. Zaɓi shi kuma danna maɓallin aiki F4. Kashi na biyu na haɗin haɗin gwiwa har yanzu yana da dangantaka. Yanzu, lokacin amfani da alamar mai cikawa, tantanin farko na kewayon za a daidaita, kuma na ƙarshe zai shimfiɗa yayin da yake ƙasa. Wannan shi ne abin da muke buƙatar cika burinmu. Bayan haka, danna maballin "Ok".



19. Don haka, sakamakon daidaiton bashin bashi bayan watan na biyu an nuna shi a cikin tantanin halitta. Yanzu, fara daga wannan tantanin, zamu kwafa dabarar cikin abubuwan mambobi marasa amfani ta hanyar amfani da alamar cikawa.



20. Lissafin kowane wata akan daidaita lamuni na tsawon rancen. Kamar yadda aka zata, a karshen lokacin wannan adadin ba komai bane.

Sabili da haka, ba kawai ƙididdige biyan a kan rancen ba, amma mun shirya nau'in lissafin aro na lamuni. Wanne zaiyi aiki akan tsarin karba-karba. Idan a cikin teburin farko mu, alal misali, canza girman rance da ƙimar biyan kuɗi na shekara-shekara, to, a teburin ƙarshe bayanan za a sake tattara bayanan ta atomatik.Sabili da haka, ana iya amfani dashi ba sau ɗaya kawai don takamaiman takaddama ba, amma ana amfani dashi a cikin yanayi daban-daban don ƙididdige zaɓuɓɓukan bashi bisa ga tsarin tsarin kuɗi.

Darasi: Ayyukan Kasuwanci a Excel

Kamar yadda kake gani, ta amfani da shirin Excel a gida, zaka iya lissafta jimlar biyan bashin kowane wata gwargwadon tsarin kowace shekara, ta amfani da mai amfani da wadannan manufofin. PMT. Bugu da kari, ta amfani da ayyuka OSPLT da KYAUTA zaku iya lissafin adadin biyan kuɗi akan jikin lamunin da sha'awa don lokacin da aka ƙayyade. Aiwatar da duk wannan jaka na ayyuka tare, yana yiwuwa ƙirƙirar ƙididdigar lamuni mai ƙarfi wanda za'a iya amfani dashi fiye da sau ɗaya don ƙididdige biyan shekara.