Suplanuokite paskolos grąžinimą naudodami „Excel“ formules

Paskolos grąžinimas yra pinigų, anksčiau pasiskolintų iš skolintojo, grąžinimas, paprastai atliekant periodinius mokėjimus, į kuriuos įeina pagrindinė suma ir palūkanos. Ar žinojai, kad apskaičiuodamas paskolos grąžinimus galite naudoti programinę įrangą „Excel“?

Šis straipsnis yra nuoseklus paskolos skaičiavimo nustatymo vadovas.

Pagrindiniai išvežamieji daiktai

• Naudokite „Excel“, kad galėtumėte tvarkyti savo hipoteką, nustatydami mėnesio įmoką, palūkanų normą ir paskolos tvarkaraštį.
• Galite išsamiau išnagrinėti paskolos pasiskirstymą su „excel“ ir sudaryti jums tinkamiausią grąžinimo grafiką.
• Kiekvienam žingsniui yra apskaičiavimai, kuriuos galite pritaikyti, kad atitiktumėte savo specifinius poreikius.
• Padaliję ir ištyrę paskolą žingsnis po žingsnio, grąžinimo procesas gali tapti ne toks sudėtingas ir labiau valdomas.

Supratimas apie jūsų hipoteką

Naudodamiesi „Excel“ galite geriau suprasti savo hipoteką atlikdami tris paprastus veiksmus. Pirmasis žingsnis nustato mėnesinę įmoką. Antrasis žingsnis apskaičiuoja palūkanų normą, o trečiasis žingsnis nustato paskolos grafiką.

„Excel“ galite sudaryti lentelę, kurioje nurodoma palūkanų norma, paskolos skaičiavimas paskolos laikotarpiui, paskolos suskaidymas, amortizacija ir mėnesinė įmoka.

Apskaičiuokite mėnesinę išmoką

Pirma, štai kaip apskaičiuoti mėnesinę hipotekos įmoką. Naudodamiesi metine palūkanų norma, pagrindine suma ir trukme galime nustatyti kas mėnesį grąžintiną sumą.

Formulė, kaip parodyta paveikslėlyje aukščiau, parašyta taip:

= -PMT (norma; ilgis; dabartinė vertė; [ateities_ vertė]; [tipas])

Minuso ženklas priešais PMT yra būtinas, nes formulė pateikia neigiamą skaičių. Pirmieji trys argumentai yra paskolos norma, paskolos trukmė (laikotarpių skaičius) ir pagrindinė pasiskolinta suma. Paskutiniai du argumentai yra neprivalomi, o likutinė vertė yra nulinė; mokėti iš anksto (už vieną) arba pabaigoje (už nulį) taip pat neprivaloma.

Mėnesio paskolos įmokai apskaičiuoti naudojama „Excel“ formulė:

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

Paaiškinimas: normai apskaičiuoti naudojame mėnesio normą (kurso periodą), tada apskaičiuojame laikotarpių skaičių (120 metų 10 metų padauginta iš 12 mėnesių) ir galiausiai nurodome pagrindinę skolintą sumą. Mūsų mėnesinė įmoka per 10 metų bus 1 161, 88 USD.

Apskaičiuokite metinę palūkanų normą

Mes matėme, kaip nustatyti mėnesio įmokos už hipoteką apskaičiavimą. Bet mes galbūt norėsime nustatyti maksimalią mėnesinę įmoką, kurią galime sau leisti. Tai taip pat rodo metų, per kuriuos turėtume grąžinti paskolą, skaičių. Dėl šios priežasties mes norėtume žinoti atitinkamą metinę palūkanų normą.

Kaip parodyta aukščiau esančiame ekrano kopijoje, pirmiausia apskaičiuojame laikotarpio normą (mūsų atveju - mėnesinę), o tada metinę normą. Naudojama formulė bus RATE, kaip parodyta aukščiau esančiame ekrano kopijoje. Jis parašytas taip:

= RATE (Nper; pmt; dabartinė vertė; [ateities_ vertė]; [tipas])

Pirmieji trys argumentai yra paskolos trukmė (laikotarpių skaičius), mėnesinė įmoka už paskolos grąžinimą ir pagrindinė pasiskolinta suma. Paskutiniai trys argumentai yra neprivalomi, o likutinė vertė yra nulinė; termino argumentas iš anksto (vienam) arba pabaigoje (lygus nuliui) taip pat neprivalomas. Galiausiai įverčio argumentas yra neprivalomas, tačiau jis gali suteikti pradinį įverčio vertę.

Kredito normai apskaičiuoti naudojama „Excel“ formulė:

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

Pastaba: atitinkami mėnesio įmokos duomenys turi būti pažymėti neigiamu ženklu. Štai kodėl prieš formulę yra minuso ženklas. Palūkanų normos laikotarpis yra 0, 294%.

Mes naudojame formulę = (1 + B5) yra 12-1 ^ = (1 + 0, 294%) ^ 12-1, kad gautume metinę paskolos normą, kuri yra 3, 58%. Kitaip tariant, norėdami pasiskolinti 120 000 USD per 13 metų ir sumokėti 960 USD per mėnesį, turėtume derėtis dėl paskolos, kurios metinė norma yra 3, 58%.

„Excel“ naudojimas yra puikus būdas sekti, ką esate skolingi, ir sudaryti grąžinimo grafiką, kuris sumažintų visus galinčius mokėti mokesčius.

Paskolos trukmės nustatymas

Dabar mes pamatysime, kaip nustatyti paskolos trukmę, kai žinote metinę palūkanų normą, pasiskolintą pagrindinę sumą ir mėnesinę įmoką. Kitaip tariant, kiek laiko mums reikės grąžinti 120 000 USD hipoteką su 3, 10% palūkanų norma ir 1100 USD mėnesinę įmoką ">

Mūsų naudojama formulė yra NPER, kaip parodyta aukščiau esančiame ekrano kopijoje, ir ji parašyta taip:

= NPER (norma; pmt; dabartinė vertė; [ateities_ vertė]; [tipas])

Pirmieji trys argumentai yra metinė paskolos norma, mėnesinė įmoka, reikalinga paskolai grąžinti, ir pagrindinė pasiskolinta suma. Paskutiniai du argumentai yra neprivalomi, o likutinė vertė yra nulinė. Terminas argumentas, mokėtinas iš anksto (už vieną) arba pabaigoje (už nulį) taip pat neprivalomas.

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

Pastaba: atitinkami mėnesio įmokos duomenys turi būti pažymėti neigiamu ženklu. Štai kodėl prieš formulę turime minuso ženklą. Kompensacija trunka 127, 97 laikotarpio (mūsų atveju - mėnesius).

Paskolos grąžinimui per metus naudosime formulę = B5 / 12 = 127, 97 / 12. Kitaip tariant, norėdami pasiskolinti 120 000 USD su 3, 10% metine norma ir mokėti 1 100 USD per mėnesį, turėtume grąžinti 128 arba 10 metų ir 8 mėnesių terminus.

Skolinantis paskolą

Paskolos mokėjimą sudaro pagrindinė suma ir palūkanos. Palūkanos skaičiuojamos už kiekvieną laikotarpį - pavyzdžiui, kas mėnesį grąžindami 10 metų, mes mokėsime 120 laikotarpių.

Aukščiau pateiktoje lentelėje parodytas paskolos (viso laikotarpis lygus 120) paskirstymas naudojant PPMT ir IPMT formules. Abiejų formulių argumentai yra vienodi ir suskirstomi taip:

= -PPMT (norma; skaičių_periodas; ilgis; pagrindinė dalis; [likutinis]; [terminas])

Argumentai yra tokie patys kaip ir jau matytos PMT formulės, išskyrus „num_period“, kuris pridedamas parodyti laikotarpį, per kurį reikia suskaidyti paskolą atsižvelgiant į pagrindinę sumą ir palūkanas. Štai pavyzdys:

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

Rezultatas parodomas ekrano kopijoje virš „Paskolų suskaidymo“ analizuojamu laikotarpiu, kuris yra „vienas“; tai yra pirmasis laikotarpis arba pirmasis mėnuo. Mes mokame 1 161, 88 USD, suskirstytą į 856, 20 USD pagrindinę sumą ir 305, 68 USD palūkanų.

Paskolų skaičiavimas „Excel“

Taip pat galima apskaičiuoti pagrindinės sumos ir palūkanų grąžinimą už kelis laikotarpius, pavyzdžiui, pirmuosius 12 mėnesių arba pirmuosius 15 mėnesių.

= -CUMPRINC (norma; ilgis; pagrindinė; pradžios_ data; pabaigos_ data; tipas)

Mes randame argumentus, normą, ilgį, pagrindinę dalį ir terminą (kurie yra privalomi), kuriuos mes jau matėme pirmoje dalyje su formule PMT. Bet čia mums taip pat reikia argumentų „start_date“ ir „end_date“. „Start_date“ nurodo analizuojamo laikotarpio pradžią, o „end_date“ nurodo analizuojamo laikotarpio pabaigą.

Štai pavyzdys:

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

Rezultatas parodytas ekrano kopijoje „Cumul 1st years“, taigi analizuojami laikotarpiai svyruoja nuo vieno iki pirmojo laikotarpio (pirmojo mėnesio) iki dvyliktojo (12-ojo mėnesio). Per metus sumokėtume 10 419, 55 USD pagrindinę sumą ir 3 522, 99 USD palūkanas.

Paskolos amortizacija

Ankstesnės formulės leidžia mums sudaryti tvarkaraštį kiekvienam laikotarpiui, žinoti, kiek mes mokėsime kiekvieną mėnesį pagrindinės sumos ir palūkanų, ir žinoti, kiek liko sumokėti.

Paskolų grafiko sudarymas

Norėdami sudaryti paskolų grafiką, naudosime skirtingas aukščiau aptartas formules ir išplėsime jas laikotarpių skaičiui.

Pirmojo periodo stulpelyje įveskite „1“ kaip pirmąjį periodą ir vilkite langelį žemyn. Mūsų atveju mums reikia 120 laikotarpių, nes 10 metų paskolos įmoka, padauginta iš 12 mėnesių, yra lygi 120.

Antrasis stulpelis yra mėnesinė suma, kurią turime mokėti kiekvieną mėnesį, kuri yra pastovi per visą paskolų grafiką. Norėdami apskaičiuoti sumą, įtraukite šią formulę į pirmojo laikotarpio langelį:

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

Trečiasis stulpelis yra pagrindinė suma, kuri bus grąžinta kas mėnesį. Pvz., 40-ajam laikotarpiui mes grąžinsime 945, 51 USD pagrindinę sumą už mėnesinę bendrą 1 161, 88 USD sumą.

Norėdami apskaičiuoti pagrindinę išpirktą sumą, naudojame šią formulę:

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

Ketvirtasis stulpelis yra palūkanos, už kurias apskaičiuodami mėnesio sumą grąžiname pagrindinę sumą, kad apskaičiuotume, kiek palūkanų reikia sumokėti:

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

Penktame stulpelyje nurodoma sumokėta suma. Pavyzdžiui, atlikę 40-ąjį mokėjimą, turėsime sumokėti 83 994, 69 USD nuo 120 000 USD.

Formulė yra tokia:

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

Formulėje naudojamas pagrindinės sumos derinys, einantis prieš langelį, kuriame yra pasiskolinta pagrindinė suma. Šis laikotarpis pradeda keistis, kai nukopijuojame ir tempiame langelį žemyn. Žemiau pateiktoje lentelėje parodyta, kad pasibaigus 120 laikotarpiams mūsų paskola bus grąžinta.