Formula don ƙididdige biyan kuɗin shekara a cikin Excel

Biyan lamuni yana da sauƙi da sauri don ƙididdigewa tare da Microsoft Office Excel. Ana kashe lokaci mai yawa akan lissafin hannu. Wannan labarin zai mayar da hankali kan biyan kuɗi na shekara-shekara, fasalin lissafin su, fa'idodi da rashin amfani.

Menene biyan kuɗin shekara

Hanyar biyan bashin kowane wata, wanda adadin da aka ajiye ba ya canzawa a duk tsawon lokacin lamuni. Wadancan. a wasu ranaku na kowane wata, mutum ya ajiye takamaiman adadin kuɗi har sai an biya bashin gaba ɗaya.

Bugu da ƙari, an riga an haɗa riba a kan lamuni a cikin adadin kuɗin da aka biya wa banki.

Rarraba shekara-shekara

Ana iya raba biyan kuɗin shekara-shekara zuwa nau'ikan masu zuwa:

1. Kafaffen Biyan kuɗi waɗanda ba sa canzawa suna da ƙayyadaddun ƙima ba tare da la'akari da yanayin waje ba.
2. Kudi. Ikon canza adadin biyan kuɗi idan faɗuwa ko tashi a cikin canjin kuɗi.
3. indexed. Biyan kuɗi dangane da matakin, alamar hauhawar farashin kaya. A lokacin lokacin lamuni, girman su yakan canza.
4. Masu canji. Annuity, wanda zai iya canzawa dangane da yanayin tsarin kuɗi, kayan aiki.

Kula! Kafaffen biyan kuɗi sun fi dacewa ga duk masu karbar bashi, saboda suna da ƙananan haɗari.

Fa'idodi da rashin amfani na biyan kuɗin shekara

Don ƙarin fahimtar batun, ya zama dole don nazarin mahimman abubuwan wannan nau'in biyan kuɗi. Yana da fa'idodi masu zuwa:

• Ƙaddamar da takamaiman adadin biyan kuɗi da kwanan watan biya.
• Babban samuwa ga masu karbar bashi. Kusan kowa na iya neman kuɗin shekara, ko da kuwa yanayin kuɗinsa.
• Yiwuwar rage yawan adadin kuɗi na wata-wata tare da karuwar hauhawar farashi.

Ba tare da drawbacks:

• Babban darajar. Mai karbar bashi zai biya babban adadin kuɗi idan aka kwatanta da biyan kuɗi na banbanta.
• Matsalolin da ke tasowa daga sha'awar biyan bashin kafin lokaci.
• Babu sakewa don biyan kuɗi da wuri.

Menene biyan bashin?

Biyan kuɗin shekara yana da abubuwa masu zuwa:

• Ribar da mutum ya biya lokacin biyan bashi.
• Sashe na babban adadin.

A sakamakon haka, jimlar yawan riba kusan ko da yaushe ya wuce adadin da mai ba da bashi ya bayar don rage bashin.

Basic Annuity Pay Formula a cikin Excel

Kamar yadda aka ambata a sama, a cikin Microsoft Office Excel zaku iya aiki tare da nau'ikan biyan kuɗi daban-daban don lamuni da ci gaba. Annuity ba togiya. Gabaɗaya, dabarar da zaku iya ƙididdige gudunmawar shekara-shekara da sauri kamar haka:  

Muhimmin! Ba shi yiwuwa a buɗe ɓangarorin a cikin ma'anar wannan magana don sauƙaƙe shi.

An yanke mahimman ƙimar tsarin kamar haka:

• AP - biyan kuɗi na shekara (an gajarta sunan).
• O – girman babban bashin mai bashi.
• PS – Ribar da wani banki ke gabatarwa kowane wata.
• C shine adadin watannin da lamunin ya kasance.

Don daidaita bayanin, ya isa ya ba da ƴan misalan amfani da wannan dabarar. Za a kara tattauna su.

Misalan amfani da aikin PMT a cikin Excel

Muna ba da yanayi mai sauƙi na matsalar. Wajibi ne a lissafta biyan bashin kowane wata idan bankin ya gabatar da sha'awa na 23%, kuma adadin adadin shine 25000 rubles. Bayar da lamuni zai ɗauki shekaru 3. Ana magance matsalar bisa ga algorithm:

1. Yi babban maƙunsar bayanai a cikin Excel bisa tushen bayanan.

2. Kunna aikin PMT kuma shigar da hujja don shi a cikin akwatin da ya dace.
3. A cikin filin "Bet", shigar da dabara "B3/B5". Wannan zai zama adadin riba akan lamuni.
4. A cikin layin "Nper" rubuta darajar a cikin nau'i "B4 * B5". Wannan zai zama jimlar adadin biyan kuɗi na tsawon lokacin lamuni.
5. Cika filin "PS". Anan kana buƙatar nuna adadin farko da aka karɓa daga banki, rubuta darajar "B2".

6. Tabbatar cewa bayan danna "Ok" a cikin teburin tushe, an ƙididdige ƙimar "Biyan kuɗi na wata-wata".

Ƙarin Bayani! Lambar mara kyau tana nuna cewa mai karɓar kuɗi yana kashe kuɗi.

Misali na ƙididdige yawan kuɗin da aka biya akan lamuni a Excel

A cikin wannan matsala, kuna buƙatar ƙididdige adadin da mutumin da ya karɓi lamuni na 50000 rubles a ƙimar riba na 27% na shekaru 5 zai wuce. Gabaɗaya, mai karɓar bashi yana biyan kuɗi 12 a kowace shekara. Magani:

1. Haɗa teburin bayanan asali.

2. Daga jimlar adadin biyan kuɗi, cire adadin farko bisa ga dabara «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Dole ne a saka shi cikin ma'aunin dabarar da ke saman babban menu na shirin.
3. A sakamakon haka, adadin ƙarin biya zai bayyana a cikin layi na ƙarshe na farantin da aka halicce. Mai karbar bashi zai biya 41606 rubles a saman.

Dabarar ƙididdige mafi kyawun biyan lamuni na wata-wata a cikin Excel

Aiki tare da yanayin da ke biyowa: abokin ciniki ya yi rajistar asusun banki don 200000 rubles tare da yiwuwar sake cika kowane wata. Wajibi ne a lissafta adadin kuɗin da mutum zai biya kowane wata, ta yadda bayan shekaru 4 yana da 2000000 rubles a cikin asusunsa. Adadin shine 11%. Magani:

1. Ƙirƙiri maƙunsar bayanai dangane da ainihin bayanan.

2. Shigar da dabarar a cikin layin shigar da Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» kuma danna "Enter" daga keyboard. Haruffa za su bambanta dangane da sel waɗanda aka sanya tebur a cikinsu.
3. Bincika cewa an ƙididdige adadin gudummawa ta atomatik a layin ƙarshe na tebur.

Kula! Don haka, don abokin ciniki ya tara 4 rubles a cikin adadin 2000000% a cikin shekaru 11, yana buƙatar saka 28188 rubles kowane wata. Rage yawan adadin yana nuna cewa abokin ciniki yana haifar da asara ta hanyar ba da kuɗi ga banki.

Fasalolin amfani da aikin PMT a cikin Excel

Gabaɗaya, an rubuta wannan tsari kamar haka: = PMT (kudi; nper; ps; [bs]; [nau'in]). Aikin yana da fasali masu zuwa:

1. Lokacin da aka ƙididdige gudunmawar kowane wata, ƙimar shekara ce kawai ake la'akari.
2. Lokacin ƙididdige ƙimar riba, yana da mahimmanci a sake ƙididdige ƙididdigewa bisa adadin adadin kuɗi a kowace shekara.
3. Maimakon hujjar "Nper" a cikin dabara, ana nuna takamaiman lamba. Wannan shine lokacin biyan kuɗi.

Lissafin biyan kuɗi

Gabaɗaya, ana ƙididdige biyan kuɗin shekara a matakai biyu. Don fahimtar batun, kowane mataki dole ne a yi la'akari da shi daban. Za a ci gaba da tattauna wannan.

Mataki na 1: lissafin kuɗin kowane wata

Don lissafta a cikin Excel adadin da kuke buƙatar biya kowane wata akan lamuni tare da ƙayyadadden ƙima, dole ne ku:

1. Haɗa teburin tushen kuma zaɓi tantanin halitta da kake son nuna sakamakon kuma danna maɓallin "Saka aikin" a saman.

2. A cikin jerin ayyuka, zaɓi "PLT" kuma danna "Ok".

3. A cikin taga na gaba, saita muhawara don aikin, yana nuna madaidaicin layi a cikin tebur da aka haɗa. A ƙarshen kowane layi, kuna buƙatar danna gunkin, sannan zaɓi tantanin halitta da ake so a cikin tsararru.

4. Lokacin da aka cika duk muhawarar, za a rubuta tsarin da ya dace a cikin layi don shigar da dabi'u, kuma sakamakon lissafin tare da alamar raguwa zai bayyana a filin "Biyan kuɗi na wata-wata".

Muhimmin! Bayan an ƙididdige kuɗin da aka biya, za a iya ƙididdige adadin kuɗin da mai karɓar bashi zai biya na tsawon lokacin lamuni.

Mataki na 2: bayanan biyan kuɗi

Ana iya ƙididdige adadin kuɗin da aka biya a kowane wata. A sakamakon haka, mutum zai fahimci yawan kuɗin da zai kashe a kan rance kowane wata. Ana yin lissafin dalla-dalla kamar haka:

1. Ƙirƙiri maɓalli na tsawon watanni 24.

2. Sanya siginan kwamfuta a cikin tantanin halitta na farko na tebur kuma saka aikin "OSPLT".

3. Cika muhawarar aikin ta hanya guda.

4. Lokacin cika filin "Lokaci", kuna buƙatar komawa zuwa wata na farko a cikin tebur, yana nuna tantanin halitta 1.

5. Bincika cewa tantanin halitta na farko a cikin shafi "Biyan kuɗi ta jikin lamuni" ya cika.
6. Don cika dukkan layuka na shafi na farko, kuna buƙatar shimfiɗa tantanin halitta zuwa ƙarshen tebur

7. Zaɓi aikin "PRPLT" don cika shafi na biyu na tebur.
8. Cika duk gardama a cikin taga da aka buɗe daidai da hoton da ke ƙasa.

9. Yi lissafin jimlar biyan kuɗi na wata-wata ta ƙara ƙima a cikin ginshiƙai biyu da suka gabata.

10. Don ƙididdige "Balance biya", kuna buƙatar ƙara yawan kuɗi zuwa biyan kuɗi a jikin rancen kuma ku shimfiɗa shi zuwa ƙarshen farantin don cika duk watanni na lamuni.

Ƙarin Bayani! Lokacin ƙididdige ragowar, dole ne a rataye alamun dala akan tsarin don kada ya fita lokacin da aka shimfiɗa shi.

Ƙididdigar biyan kuɗi a kan lamuni a cikin Excel

Aikin PMT yana da alhakin ƙididdige yawan kuɗin shiga a cikin Excel. Ka'idar lissafi gabaɗaya ita ce aiwatar da matakai masu zuwa:

1. Haɗa teburin bayanan asali.
2. Gina jadawalin biyan bashi na kowane wata.
3. Zaɓi tantanin halitta na farko a cikin shafi "Biyan kuɗi akan lamuni" kuma shigar da tsarin lissafin "PLT ($ B3/12; $ B$4; $ B$2)".
4. An shimfiɗa ƙimar da aka samu don duk ginshiƙan farantin.

Lissafi a cikin MS Excel biyan kuɗin babban adadin bashin

Dole ne a biya kuɗin shekara a kowane wata a ƙayyadaddun adadin kuɗi. Kuma yawan riba ba ya canzawa.

Lissafin ma'auni na babban adadin (tare da BS=0, type=0)

A ce an ɗauki rance na 100000 rubles na shekaru 10 a 9%. Wajibi ne a lissafta adadin babban bashin a cikin watan 1 na shekara ta 3rd. Magani:

1. Haɗa takardar bayanan da lissafin biyan kuɗi na wata-wata ta amfani da dabarar PV na sama.
2. Yi lissafin rabon kuɗin da ake buƙata don biyan wani ɓangare na bashin ta amfani da dabara "= -PMT-(PS-PS1)* abu = -PMT-(PS +PMT+PS* abu)".
3. Yi lissafin adadin babban bashi na tsawon lokaci 120 ta amfani da sanannen dabara.
4. Amfani da ma'aikacin HPMT nemo adadin ribar da aka biya na wata na 25.
5. Duba sakamakon.

Ƙididdigar adadin kuɗin da aka biya tsakanin lokaci biyu

An fi yin wannan lissafin ta hanya mai sauƙi. Kuna buƙatar amfani da waɗannan ƙididdiga masu zuwa don ƙididdige adadin a cikin tazara na lokuta biyu:

• ="-BS(abu; con_period; plt; [ps]; [nau'in]) / (nau'in 1+ * abu)".
• = "+ BS (kudi; farkon_period-1; plt; [ps]; [nau'in]) / IF (lokacin farawa = 1; 1; 1 + nau'in * ƙimar)".

Kula! Ana maye gurbin haruffa a cikin baka da takamaiman ƙima.

Biyan kuɗi da wuri tare da rage lokaci ko biyan kuɗi

Idan kuna buƙatar rage lokacin lamuni, dole ne ku yi ƙarin ƙididdiga ta amfani da ma'aikacin IF. Don haka zai yiwu a sarrafa ma'aunin sifili, wanda bai kamata a kai ba kafin ƙarshen lokacin biyan kuɗi.

Don rage biyan kuɗi, kuna buƙatar sake ƙididdige gudummawar kowane wata da ya gabata.

Kalkuleta na aro tare da biyan kuɗi na yau da kullun

Akwai zaɓuɓɓukan shekara-shekara da yawa inda mai karɓar bashi zai iya saka adadin adadin kuɗi a kowace rana na wata. A irin wannan yanayi, ana ƙididdige ma'auni na bashin da riba ga kowace rana. A lokaci guda a cikin Excel kuna buƙatar:

1. Shigar da kwanakin watan da ake biyan kuɗi, kuma nuna lambar su.
2. Bincika adadin mara kyau da inganci. An fi son marasa kyau.
3. Ƙididdige kwanakin tsakanin ranaku biyu da aka ajiye kuɗi.

Lissafin biyan kuɗi na lokaci-lokaci a cikin MS Excel. Adadin lokaci

A cikin Excel, zaku iya ƙididdige adadin adadin kuɗi na yau da kullun, muddin an riga an tara ƙayyadaddun adadin. Ana yin wannan aikin ta amfani da aikin PMT bayan an haɗa teburin farko.

Kammalawa

Don haka, biyan kuɗi na shekara-shekara yana da sauƙi, sauri kuma mafi inganci don ƙididdigewa a cikin Excel. Ma'aikacin PMT ne ke da alhakin lissafin su. Ana iya samun ƙarin cikakkun misalai a sama.