Ieplānojiet aizdevuma atmaksu, izmantojot Excel formulas

Aizdevuma atmaksa ir naudas atmaksa, kas iepriekš aizgūta no aizdevēja, parasti veicot periodiskus maksājumus, kas ietver pamatsummu un procentus. Vai zinājāt, ka aizdevuma atmaksu aprēķināšanai varat izmantot programmatūru Excel?

Šis raksts ir detalizēts ceļvedis aizdevuma aprēķinu iestatīšanai.

Taustiņu izņemšana

• Izmantojiet programmu Excel, lai apstrādātu jūsu hipotēku, nosakot ikmēneša maksājumu, procentu likmi un aizdevuma grafiku.
• Varat sīkāk izpētīt aizdevuma sadalījumu ar excel un izveidot atmaksas grafiku, kas jums der.
• Katram solim ir pieejami aprēķini, kurus varat pielāgot savām īpašajām vajadzībām.
• Pakāpeniski sadalot un pārbaudot aizdevumu, atmaksas process var justies mazāk apgrūtinošs un vieglāk vadāms.

Izpratne par jūsu hipotēku

Izmantojot programmu Excel, jūs varat labāk izprast hipotēku trīs vienkāršās darbībās. Pirmais solis nosaka ikmēneša maksājumu. Otrajā posmā tiek aprēķināta procentu likme, un trešajā solī tiek noteikts aizdevuma grafiks.

Jūs varat izveidot tabulu programmā Excel, kurā būs norādīta procentu likme, aizdevuma aprēķins aizdevuma termiņam, aizdevuma sadalījums, amortizācija un ikmēneša maksājums.

Aprēķiniet ikmēneša maksājumu

Pirmkārt, šeit ir aprakstīts, kā aprēķināt ikmēneša maksājumu par hipotēku. Izmantojot gada procentu likmi, pamatsummu un ilgumu, mēs varam noteikt ik mēnesi atmaksājamo summu.

Formula, kā parādīts ekrānuzņēmumā, ir uzrakstīta šādi:

= -PMT (likme; garums; pašreizējā vērtība; [nākotnes_vērtība]; [tips])

Mīnusa zīme PMT priekšā ir nepieciešama, jo formula atgriež negatīvu skaitli. Pirmie trīs argumenti ir aizdevuma likme, aizdevuma ilgums (periodu skaits) un aizņemtā pamatsumma. Pēdējie divi argumenti nav obligāti, atlikušās vērtības noklusējuma vērtība ir nulle; maksājams avansā (par vienu) vai beigās (par nulli), arī nav obligāts.

Aizdevuma ikmēneša maksājuma aprēķināšanai izmantotā Excel formula ir:

= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Paskaidrojums: likmei mēs izmantojam mēneša likmi (likmes periodu), tad mēs aprēķinām periodu skaitu (120 uz 10 gadiem, kas reizināts ar 12 mēnešiem), un, visbeidzot, mēs norādām aizņemto pamatsummu. Mūsu ikmēneša maksājums 10 gadu laikā būs 1 161, 88 USD.

Aprēķiniet gada procentu likmi

Mēs esam redzējuši, kā iestatīt hipotēkas ikmēneša maksājuma aprēķinu. Bet mēs varētu vēlēties noteikt maksimālo ikmēneša maksājumu, ko varam atļauties, un tas parāda arī gadu skaitu, par kuru mums būtu jāatmaksā aizdevums. Šī iemesla dēļ mēs vēlamies uzzināt atbilstošo gada procentu likmi.

Kā parādīts iepriekš redzamajā ekrānuzņēmumā, vispirms mēs aprēķinām perioda likmi (mūsu gadījumā - mēnesī) un pēc tam gada likmi. Izmantotā formula būs RATE, kā parādīts iepriekš redzamajā ekrānuzņēmumā. Tas ir rakstīts šādi:

= RATE (Nper; pmt; present_value; [future_value]; [type])

Pirmie trīs argumenti ir aizdevuma ilgums (periodu skaits), ikmēneša maksājums aizdevuma atmaksai un aizņemtā pamatsumma. Pēdējie trīs argumenti nav obligāti, un atlikušās vērtības noklusējuma vērtība ir nulle; termins arguments, lai pārvaldītu termiņu iepriekš (vienam) vai beigās (nullei), arī nav obligāts. Visbeidzot, aplēses arguments nav obligāts, bet tas var sniegt sākotnēju likmes aplēsi.

Aizdevuma likmes aprēķināšanai izmantotā Excel formula ir:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Piezīme: atbilstošajiem ikmēneša maksājuma datiem jābūt ar negatīvu zīmi. Tāpēc pirms formulas ir mīnusa zīme. Likmes periods ir 0, 294%.

Mēs izmantojam formulu = (1 + B5) ir 12-1 ^ = (1 + 0, 294%) ^ 12-1, lai iegūtu mūsu aizdevuma gada likmi, kas ir 3, 58%. Citiem vārdiem sakot, lai aizņemtos USD 120 000 13 gadu laikā un maksātu USD 960 mēnesī, mums jāvienojas par aizdevumu ar maksimālo gada likmi 3, 58%.

Programmas Excel izmantošana ir lielisks veids, kā izsekot tam, ko esat parādā, un nākt klajā ar atmaksas grafiku, kas samazina visas iespējamās maksas, kuras jums varētu būt pienākas.

Aizdevuma ilguma noteikšana

Tagad mēs redzēsim, kā noteikt aizdevuma ilgumu, kad jūs zināt gada procentu likmi, aizņēmuma pamatsummu un ikmēneša maksājumu, kas ir jāatmaksā. Citiem vārdiem sakot, cik ilgi mums būs jāatmaksā hipotēka USD 120 000 apmērā ar likmi 3, 10% un ikmēneša maksājums USD 1, 100 ">

Formula, kuru mēs izmantosim, ir NPER, kā parādīts iepriekš redzamajā ekrānuzņēmumā, un tā ir uzrakstīta šādi:

= NPER (likme; pmt; pašreizējā vērtība; [nākotnes_vērtība]; [tips])

Pirmie trīs argumenti ir aizdevuma gada likme, ikmēneša maksājums, kas nepieciešams aizdevuma atmaksai, un aizņēmuma pamatsumma. Pēdējie divi argumenti nav obligāti, atlikušās vērtības noklusējuma vērtība ir nulle. Arī termins arguments, kas maksājams avansā (par vienu) vai beigās (par nulli), nav obligāts.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

Piezīme: atbilstošajiem ikmēneša maksājuma datiem jābūt ar negatīvu zīmi. Tāpēc pirms formulas mums ir mīnusa zīme. Kompensācijas ilgums ir 127, 97 periodi (mūsu gadījumā mēneši).

Par gadu skaitu aizdevuma atmaksai izmantosim formulu = B5 / 12 = 127, 97 / 12. Citiem vārdiem sakot, lai aizņemtos USD 120 000 ar gada likmi 3, 10% un maksātu USD 1100 mēnesī, mums vajadzētu atmaksāt termiņus 128 mēnešus vai 10 gadus un 8 mēnešus.

Kredīta sadalīšana

Aizdevuma maksājumu veido pamatsumma un procenti. Procenti tiek aprēķināti par katru periodu, piemēram, ikmēneša maksājumi 10 gadu laikā mums dos 120 periodus.

Iepriekš esošajā tabulā parādīts aizdevuma sadalījums (kopējais periods vienāds ar 120), izmantojot PPMT un IPMT formulas. Abu formulu argumenti ir vienādi un ir sadalīti šādi:

= -PPMT (likme; numuru_periods; garums; pamatsumma; [atlikusī]; [termiņš])

Argumenti ir tādi paši kā jau redzētajai PMT formulai, izņemot "num_period", kas tiek pievienots, lai parādītu periodu, kurā jāsadala aizdevums, ņemot vērā pamatsummu un procentus. Šis ir piemērs:

= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)

Rezultāts ir parādīts ekrānuzņēmumā virs “Kredīta sadalīšanās” analizētajā periodā, kas ir “viens”; tas ir, pirmais periods vai pirmais mēnesis. Mēs maksājam 1 161, 88 USD, kas sadalīti pamatsummā 856, 20 USD un procentos 305, 68 USD.

Kredīta aprēķināšana programmā Excel

Ir arī iespējams aprēķināt pamatsummas un procentu atmaksu vairākiem periodiem, piemēram, pirmajiem 12 mēnešiem vai pirmajiem 15 mēnešiem.

= -CUMPRINC (likme; garums; pamatsumma; sākuma_datums; beigu_datums; tips)

Mēs atrodam argumentus, likmi, garumu, pamatsummu un terminu (kas ir obligāti), ko mēs jau redzējām pirmajā daļā ar formulu PMT. Bet šeit ir nepieciešami arī argumenti “sākuma_datums” un “beigu_datums”. "Sākuma_datums" norāda analizējamā perioda sākumu, un "beigu_datums" norāda uz analizējamā perioda beigām.

Šis ir piemērs:

= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

Rezultāts tiek parādīts ekrānuzņēmumā "Cumul 1. gads", tāpēc analizētie periodi svārstās no viena perioda līdz pirmā perioda (pirmais mēnesis) līdz divpadsmitā (12. mēnesis). Gada laikā mēs samaksātu USD 10 419, 55 pamatsummā un 3 522, 99 USD procentos.

Kredīta amortizācija

Iepriekšējās formulas ļauj mums izveidot grafiku pa periodiem, zināt, cik daudz mēs maksāsim pamatsummu un procentus mēnesī, un zināt, cik daudz ir jāmaksā.

Aizdevuma grafika izveidošana

Lai izveidotu aizdevuma grafiku, mēs izmantosim dažādas iepriekš aprakstītās formulas un izvērsīsim tās pa periodiem.

Pirmā perioda kolonnā kā pirmo periodu ievadiet "1" un pēc tam velciet šūnu uz leju. Mūsu gadījumā mums ir nepieciešami 120 periodi, jo 10 gadu aizdevuma maksājums, kas reizināts ar 12 mēnešiem, ir vienāds ar 120.

Otrā kolonna ir ikmēneša summa, kas mums jāmaksā katru mēnesi - tā ir nemainīga visā aizdevuma grafikā. Lai aprēķinātu summu, mūsu pirmā perioda šūnā ievietojiet šādu formulu:

= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Trešā kolonna ir pamatsumma, kas tiks atmaksāta katru mēnesi. Piemēram, par 40. periodu mēs atmaksāsim USD 945, 51 pamatsummu no mūsu ikmēneša kopsummas USD 1, 161, 88.

Lai aprēķinātu izpirkto pamatsummu, mēs izmantojam šādu formulu:

= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

Ceturtā kolonna ir procenti, par kuriem mēs izmantojam formulu, lai aprēķinātu atmaksātās pamatsummas summu no mūsu ikmēneša summas, lai uzzinātu, cik daudz procentu ir jāmaksā:

= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

Piektajā kolonnā ir norādīta summa, kas jāmaksā. Piemēram, pēc 40. maksājuma mums būs jāmaksā USD 83 994, 69 par USD 120 000.

Formula ir šāda:

= B $ 3 USD + CUMPRINC (TP; B B $ 4 * 12; B B $ 3; 1; A18; 0)

Formulā tiek izmantota pamatsummas kombinācija laika posmā pirms šūnas, kurā atrodas aizņēmuma pamatsumma. Šis periods sāk mainīties, kad nokopējam un velkam šūnu uz leju. Zemāk redzamā tabula parāda, ka 120 periodu beigās mūsu aizdevums ir atmaksāts.