Formula yo kubara amafaranga yo hejuru muri excel

Kwishura ku nguzanyo biraryoroshye kandi byihuse kubara ku biro bya Microsoft Excel. Kubara intoki bigenda cyane. Iyi ngingo ivuga ku kwishyura buri mwaka, ibiranga kubara, ibyiza n'ibibi.

Ni ubuhe butumwa bwo kwishyura buri mwaka

Uburyo bwo kwishyura buri kwezi inguzanyo, aho amafaranga yatanzwe atahinduka mugihe cyose cy'inguzanyo. Abo. Umuntu kumubare umwe wa buri kwezi atuma amafaranga yihariye kugeza igihe inguzanyo izimye.

Ibyiciro Annuita

Amafaranga yo kwishyura mu mwaka ashobora kugabanywa mu bwoko bukurikira:

1. Byakosowe. Kwishura bidahinduka bifite igipimo cyagenwe utitaye kumiterere yo hanze.
2. Ifaranga. Ibishoboka byo guhindura ubunini bwishyuwe mugihe utagabanutse cyangwa wongera igipimo cyamafaranga.
3. Igipimo. Kwishura bitewe nurwego, icyerekezo cyo guta agaciro. Mugihe cyo gutanga inguzanyo, ingano yabo irahinduka.
4. Impinduka. Umwaka, bishobora guhinduka bitewe na leta yimari, ibikoresho.

Ibyiza nibibi byo kwishyura amafaranga

Kugira ngo usobanukirwe neza ingingo, birakenewe kwiga ibintu byingenzi byubu bwoko bwishyurwa inguzanyo. Ifite ibyiza bikurikira:

• Shiraho umubare wihariye wishyuwe nitariki yatanzwe.
• Kuboneka cyane kubahawe inguzanyo. Umuntu wese uwo ari we wese azashobora gutegura isanzwe, atitaye kumwanya wacyo.
• Amahirwe yo kugabanya umubare wintererano ya buri kwezi no kongera ifaranga.

Nta nenge, ntabwo byari:

• Igipimo kinini. Uwagurijwe arenga amafaranga menshi ugereranije no kwishyura amoko.
• Ibibazo bituruka ku cyifuzo cyo gucungura umwenda imbere ya gahunda.
• Kubura kwisubiraho kugirango wishyure hakiri kare.

Kwishura ku nguzanyo ni iki?

Kwishura buri mwaka bifite ibice bikurikira:

• Inyungu zasabwe n'umugabo mugihe wishyuye inguzanyo.
• Igice cyamafaranga nkuru.

Nkigisubizo, umubare rusange wijana hafi buri gihe urenze amafaranga yatanzwe nuwagurijwe kugirango ugabanye umwenda.

Formula yibanze yuburebure bwishyuwe muri excel

Nkuko byavuzwe haruguru, muri Microsoft Ibiro bya Microsoft, urashobora gukorana nubwoko butandukanye bwinguzanyo ninguzanyo. Umwaka ntabwo ari ibintu bidasanzwe. Muri formula rusange, ushobora kubara vuba umusanzu wumwaka, birasa nkibi:

Indangagaciro nyamukuru za formula zarimbishijwe kuburyo ikurikira:

• AP - Kwishura buri mwaka (izina riragabanuka).
• O nubunini bw'umwenda nyamukuru w'uwagurijwe.
• PS - Igipimo cyinyungu gishyirwa imbere na banki ya buri kwezi.
• C ni umubare wamezi, mugihe inguzanyo.

Kugira ngo ugaragaze amakuru, birahagije kuzana ingero zimwe zo gukoresha iyi formula. Bazaganirwaho.

Ingero zo gukoresha imikorere ya PT muri Excel

Dutanga imiterere yoroshye. Birakenewe kubara inguzanyo ya buri kwezi niba banki yashyize imbere ijanisha rya 23%, kandi amafaranga yose ni amafaranga 25.000. Gutanga inguzanyo bizamara imyaka 3. Igikorwa cyakemutse ukurikije algorithm:

1. Kora imbonerahamwe isanzwe muri excel kumakuru yinkomoko.

Imbonerahamwe, yakozwe nuburyo bwikibazo. Mubyukuri, urashobora gukoresha izindi nkingi kugirango zikire.

1. Koresha imikorere ya PL hanyuma winjire ku mpaka kuri idirishya rikwiye.
2. Mu murima "igipimo" cyo kwandikisha formula "B3 / B5". Ibi bizaba igipimo cyinyungu ku gutanga inguzanyo.
3. Muri "Cper" kugirango wandike agaciro muburyo bwa "B4 * B5". Bizaba umubare rusange wishyurwa mugihe cyose cy'inguzanyo.
4. Uzuza umurima wa PS. Hano ukeneye kwerekana amafaranga yambere yafashwe muri banki, avuga ibisobanuro bya "B2".

Intambwe zikenewe mu "mikorere y'impaka". Dore inzira yo kuzuza buri parameter

1. Menya neza ko nyuma yo gukanda "OK" mumeza yinkomoko, agaciro "kwishyura buri kwezi".

Ibisubizo byanyuma. Kwishura buri kwezi birafatwa kandi bigaragazwa nurugero rutukura rwo kubara inguzanyo yishyuwe muri Excel

Muri iki gikorwa, birakenewe kubara amafaranga umuntu arenze urugero, yafashe inguzanyo ya 50.000 mu nyungu za 27% imyaka 5. Muri rusange, uwagurijwe atanga amafaranga 12. Icyemezo:

1. Kora isoko yamakuru.

Imbonerahamwe yashushanijwe nuburyo bwikibazo

1. Mubyiciro byose byishyuwe, fata umubare wambere wamafaranga ukoresheje formula "= abs (ppt (P3 / B5; B4 * B5) -B2". Igomba kwinjizwa mumirongo umurongo hejuru ya menu nkuru ya gahunda.
2. Nkigisubizo, umubare winyongera uzagaragara kumurongo wanyuma wisahani yaremye. Uwagurijwe ahembwa 41606 kuri hejuru.

Ibisubizo byanyuma. Mubyukuri inshuro ebyiri zishyuha kuri formula yo kubara igihe cyo kwishyura inguzanyo ya buri kwezi muri Excel

Umurimo ufite imiterere nkiyi: Umukiriya yanditse konti ya banki ku mafaranga 200.000 hamwe nibishoboka byo kuzuza buri kwezi. Birakenewe kubara ubwishyu umuntu agomba gukora buri kwezi kugirango mu myaka 4 yagaragaye kuba amafaranga 2000. Igipimo ni 11%. Icyemezo:

1. Kora ikimenyetso ku makuru y'inkomoko.

Imbonerahamwe yakozwe namakuru kuva mubibazo

1. Ku murongo wa winjiza Excel winjire kuri formula "= ppt (B3 / B5; B6 *; -b2; hanyuma ukande" enter "uhereye kuri clavier. Amabaruwa azatandukana bitewe na selile ameza aherereye.
2. Reba neza ko imisanzu ihita ibarwa kumurongo wanyuma wimeza.

Ibisubizo byanyuma byo kubara ibiranga ukoresheje imikorere ya PT muri Excel

Muri rusange, iyi formula yanditse ku buryo bukurikira: = PPT (igipimo; CPP; Zab; [BS]; [ubwoko]; [ubwoko]; [ubwoko]; [ubwoko]; [ubwoko]; Igikorwa gifite ibintu bikurikira:

1. Iyo imisanzu ya buri kwezi ibarwa, igipimo cyumwaka cyihariye gisuzumwa.
2. Kwerekana umubare w'inyungu, ni ngombwa kwibutsa, ukurikije umubare w'imisanzu y'umwaka.
3. Aho kuba impaka "KPER", umubare wihariye ugaragazwa muri formula. Iki nikibazo cyo kwishyura nideni.

Kubara ubwishyu

Muri rusange, kwishyurwa numwaka ubarwa mubyiciro bibiri. Kugira ngo wumve ingingo, buri cyiciro kigomba gusuzumwa ukwayo. Ibi bizaganirwaho kurushaho.

Intambwe ya 1: Kubara imisanzu ya buri kwezi

Kubara amafaranga muri Excel kugirango bikorerwe buri kwezi ku nguzanyo hamwe nigipimo cyagenwe, birakenewe:

1. Kora imbonerahamwe yisoko hanyuma uhitemo selile ushaka gusohoka ibisubizo hanyuma ukande kuri buto "andika imikorere" kuva hejuru.

Ibikorwa byambere

1. Kurutonde rwimikorere, hitamo "PLT" hanyuma ukande "OK".

Hitamo imikorere mumadirishya yihariye

1. Mu idirishya rikurikira, shiraho ingingo zikorwa, zerekana imirongo ihuye mumeza yagenwe. Kurangiza buri murongo, birakenewe gukanda igishushanyo, hanyuma ugaragaze selile wifuza muri array.

Algorithm yibikorwa kugirango yuzuze ingingo za "PLT"

1. Iyo impaka zose zuzuyemo, formulaire ihuye nayo izagabanywa kumurongo kugirango yinjire kumeza, kandi ibisubizo byibarirwa bizagaragara mu mbonerahamwe ya "Kwishura buri kwezi".

Kubara Kubara Kubara Icyiciro cya 2: Ibisobanuro byo Kwishura

Umubare w'ikirenga urashobora kubarwa buri kwezi. Nkigisubizo, umuntu azumva amafaranga buri kwezi azakoresha ku nguzanyo. Kubara ibisobanuro birambuye ni ibi bikurikira:

1. Kora imbonerahamwe yumwimerere mumezi 24.

Intangiriro ya Tacle

1. Shira indanga muri selire ya mbere yimeza hanyuma ushireho imikorere ya opplt.

Guhitamo imikorere yo kwishyura

1. Uzuza imikorere ingingo muburyo bumwe.

Kuzuza imirongo yose mumadirishya yimpaka zumukoresha e

1. Mugihe wuzuza umurima "igihe", ugomba kwerekeza ku kwezi kwambere mu isahani, byerekana selile 1.

Kuzuza Impaka "Igihe"

1. Reba neza ko selile ya mbere muri "kwishyura inguzanyo" yuzuye.
2. Kuzuza imigozi yose yinkingi yambere, birakenewe kurambura akagari kugeza kumpera yimeza

Kuzuza imirongo isigaye

1. Hitamo imikorere "PRT" kugirango wuzuze inkingi ya kabiri yimeza.
2. Uzuza ingingo zose mu idirishya rifungura ukurikije amashusho hepfo.

Kuzuza ingingo za "Umukoresha wa Prt"

1. Kubara muri rusange ubwishyu buri kwezi wiziritse indangagaciro ebyiri zabanjirije.

Kubara imisanzu ya buri kwezi

1. Kubara "kuringaniza ubwishyu", ni ngombwa kuzinga inyungu hamwe nubwishyu numubiri winguzanyo hanyuma urambuye kugeza isahani yo kuzuza amezi yose yo gutanga inguzanyo.

Kubara impirimbanyi

Kubara amafaranga yo kwishyura amafaranga ku nguzanyo muri Excel

Kubabara buri mwaka muri Excel byujuje imikorere ya PL. Ihame ryo kubara muri rusange riri mu ntambwe zikurikira:

1. Kora isoko yamakuru.
2. Kubaka gahunda yo kwishyura imyenda kuri buri kwezi.
3. Hitamo akagari ka mbere muri "Inkingi yishyurwa" hanyuma utangire "PLT ($ B3 / 12; $ 4; $ 2) ITANGAZO RY'IBIKORWA."
4. Agaciro kavuyemo kurambura isahani zose.

Igisubizo cyimikorere yo kubara plt muri MS Excel kwishyura umwenda nyamukuru

Kwishura buri mwaka bigomba gukorwa buri kwezi ku bwinshi. Byongeye kandi, igipimo cyinyungu ntigihinduka.

Kubara amafaranga asigaye yimyenda yibanze (kuri BS = 0, andika = 0)

Dufate ko inguzanyo yimirongo 100.000 yafashwe imyaka 10 kuri 9%. Birakenewe kubara amafaranga yumuyobozi mukuru mukwezi 1. Icyemezo:

1. Kora imbonerahamwe yamakuru hanyuma ubare ubwishyu buri kwezi kuri PS formula.
2. Kubara umugabane wubwishyu bikenewe kugirango wishyure igice cyimyenda, ukurikije formulaire "= -pht- (PS - PS1) * Igipimo = (PS * PM *)".
3. Kubara umubare wimyenda yibanze mugihe cya 120 ukurikije formulaire izwi.
4. Gukoresha PRT ukoresha kugirango ubone umubare winyungu wishyuwe amezi 25.
5. Reba ibisubizo.

Kubara umubare w'amadeni y'ingenzi wishyuwe mu gihe kiri hagati y'ibihe bibiri

Uku kubara nibyiza gukorwa muburyo bworoshye. Ni ngombwa gukoresha formulaire ikurikira yo kubara amafaranga mugihe cyiminsi ibiri:

• = "- bs (igipimo; kon_teriood; pll; [Zab]; [ubwoko]) / (1 + ubwoko * bet)."
• = "+ Bs (igipimo; nach_Persiod-1; [Zab;

Kwishyura hakiri kare hamwe no kugabanuka mugihe cyangwa kwishyura

Niba ukeneye kugabanya igihe cyinguzanyo, ugomba kubyara inzitizi ukoresheje umukoresha niba. Ibi birashobora kugenzurwa na zeru, bidakwiye kugerwaho mbere yigihe cyo kwishyura.

Kwishyura hakiri kare hamwe no kugabanuka muri manda

Kugabanya ubwishyu, ugomba kuvuga amafaranga kuri buri kwezi gushize.

Kugabanya Kwishura Kwishura Inguzanyo Kubara hamwe nishyuwe bidasanzwe

Hano hari amahitamo menshi yo kuriraho mugihe uwagurijwe ashobora gukora amafaranga adakemuka kumunsi wose wukwezi. Mu bihe nk'ibi, impirimbanyi y'imyenda n'inyungu bifatwa na buri munsi. Mugihe kimwe na Excele birakenewe:

1. Injiza amezi menshi yishyurwa, hanyuma ugaragaze ubwinshi.
2. Reba amafaranga mabi kandi meza. Byiza cyane.
3. Kubara iminsi hagati yamatariki abiri aho amafaranga yakozwe.

Kubara ubwishyu buri gihe muri Madamu Excel. Umusanzu wihutirwa

Muri Excel, urashobora kubara vuba ingano yishyurwa buri gihe, mugihe amafaranga yagenwe yamaze kwegeranya. Iki gikorwa gikorwa ukoresheje imikorere ya PL nyuma yimeza yambere irategurwa.

Umwanzuro

Rero, kwishyura buri mwaka byoroshye, byihuse kandi byiza cyane kubara kuri Excel. Umukoresha wa PL ashinzwe kubara. Ingero zirambuye zirashobora kuboneka hejuru.

Ubutumwa bwubutumwa bwo kubara ubwishyu bwumwaka muri Excel bwagaragaye bwa mbere kuri tekinoroji yamakuru.