Wist u dat u Excel kunt gebruiken om uw terugbetalingen van leningen te berekenen? Dit artikel zal u door alle stappen leiden die nodig zijn om dit te doen. (Zie ook: Hypotheekcalculators: Hoe ze werken .)

Met Excel kunt u uw hypotheek in drie eenvoudige stappen beter begrijpen. De eerste stap is om de maandelijkse betaling te bepalen. De tweede is om de rente te ontdekken, en de derde is om het leenschema te vinden. Hiertoe kunt u een tabel in Excel maken met de volgende gegevens: De laagste tarieven; de leningberekening voor de duur; ontbinding van een lening, evenals aflossing en berekening voor maandelijkse huur.

AD:

Leningberekening voor maandelijkse huur

Laten we eerst eens kijken hoe we de berekening van een maandelijkse betaling voor een hypotheek kunnen uitvoeren. Met andere woorden, met behulp van het jaarlijkse Iinterest-tarief, de hoofdsom en de duur, kunnen we het bedrag bepalen dat maandelijks moet worden terugbetaald.

De formule, zoals weergegeven in de schermafdruk hierboven, is als volgt geschreven:

AD:

= - PMT (snelheid; lengte; huidige waarde; [toekomstige_waarde]; [type])

Het minteken vóór PMT is noodzakelijk, omdat de formule een negatief getal oplevert. De eerste drie argumenten zijn het percentage van de lening, de lengte van de lening (aantal perioden) en het geleende kapitaal. De laatste twee argumenten zijn optioneel, de restwaarde staat standaard op 0, vooraf te betalen (voor 1) of aan het einde (voor 0), is ook optioneel.

AD:

De Excel-formule die wordt gebruikt om de maandelijkse betaling van de lening te berekenen is:

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

Toelichting: Voor de koers gebruiken we de periode van het tarief, dat is de maandelijkse koers, dan berekenen we het aantal perioden (maanden hier 120 voor 10 jaar vermenigvuldigd met 12 maanden) en ten slotte geven we aan dat de hoofdsom is geleend. Onze maandelijkse betaling bedraagt ​​$ 1, 161. 88 over 10 jaar.

Hypotheekberekening rentetarieven

We hebben gezien hoe de berekening van een maandelijkse betaling voor een hypotheek kan worden opgezet. Maar we willen misschien een maximale maandelijkse betaling instellen die we ons kunnen veroorloven en die ook het aantal jaren aangeeft waarover we het zouden moeten terugbetalen. Om die reden willen we graag de bijbehorende jaarlijkse rente kennen.

De rentevoet berekenen voor een lening

Zoals in de bovenstaande schermafbeelding wordt weergegeven, berekenen we eerst het periodetarief (maandelijks in ons geval) en vervolgens het jaartarief. De gebruikte formule is RATE, zoals weergegeven in de bovenstaande schermafbeelding. Het is als volgt geschreven:

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

De eerste drie argumenten zijn de lengte van de lening (aantal perioden) en de maandelijkse betaling om de geleende hoofdsom terug te betalen. De laatste drie argumenten zijn optioneel en de restwaarde staat standaard op 0, de term argument voor het beheer van de looptijd vooraf (voor 1) of aan het einde (voor 0) is ook optioneel, en ten slotte is het schattingsargument optioneel, maar kan geef een eerste schatting van het tarief.

De Excel-formule die wordt gebruikt om de uitleenratio te berekenen is:

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

Opmerking: de overeenkomstige gegevens in de maandelijkse betaling moet een negatief teken krijgen. Dit is de reden waarom een ​​minteken vóór de formule staat. Onze renteperiode is 0. 294%.

We gebruiken de formule = (1 + B5) is 12-1 ^ = (1 + 0. 294%) ^ 12-1 om het jaarlijkse tarief van onze lening op te trekken tot 3. 58 %. Met andere woorden, om 120.000 $ te lenen over 13 jaar om maandelijks 960 $ te betalen, moeten we onderhandelen over een lening tegen een maximale rente van maximaal 3. 58%.

Hypotheekcompensatie voor de lengte van een lening

We zullen nu bekijken hoe u de lengte van een lening kunt krijgen als u weet hoeveel rente u jaarlijks moet betalen, wat de hoofdsom is die is geleend en hoeveel maandelijks de lening moet worden terugbetaald. Met andere woorden, hoe lang zullen we een hypotheek van $ 120.000 moeten terugbetalen met een tarief van 3. 10% en een maandelijkse betaling van $ 1, 100?

Aantal terugbetalingen voor een lening

De formule die we zullen gebruiken is NPER, zoals weergegeven in de bovenstaande schermafbeelding, en het is als volgt geschreven:

= NPER (koers; pmt; presentwaarde; [toekomstwaarde]; [type])

De eerste drie argumenten zijn het jaarlijkse percentage van de lening, de maandelijkse betaling die nodig is om de lening terug te betalen en de geleende hoofdsom. De laatste twee argumenten zijn optioneel, de restwaarde staat standaard op 0, de term argument vooraf betaald (voor 1) of aan het einde (voor 0) is ook optioneel.

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

Opmerking: de bijbehorende gegevens in de maandelijkse betaling moeten een negatief teken krijgen. Dit is de reden waarom we vóór de formule een minteken hebben. De vergoeding duur is 127. 97 periodes (maanden in ons geval).

We gebruiken de formule = B5 / 12 = 127. 97/12 voor het aantal jaren dat de terugbetaling van de lening voltooid is. Met andere woorden, om $ 120.000 te lenen, met een jaarlijks tarief van 3.10% en om $ 1, 100 per maand te betalen, moeten we de vervaldagen terugbetalen voor 128 maanden of 10 jaar en 8 maanden.

De lening ontbinden

Een leningbetaling bestaat uit twee dingen, de hoofdsom en rente. De rente wordt berekend voor elke periode, bijvoorbeeld de maandelijkse aflossingen over 10 jaar, geeft ons 120 perioden.

De bovenstaande schermafbeelding toont de uitsplitsing van een lening (een totale periode gelijk aan 120) met behulp van de PPMT- en IPMT-formules. De argumenten van de twee formules zijn hetzelfde en worden als volgt onderverdeeld:

= - PPMT (snelheid; aantal_periode; lengte; opdracht; [resterend]; [terme])

= - INTPER (snelheid; aantal_periode; length; principal; [residual]; [terme])

De argumenten zijn dezelfde als die voor de PMT-formule in het eerste deel, behalve voor num_period die is toegevoegd om de periode weer te geven waarop de lening kan worden afgebroken, waardoor het belangrijkste en de interesse ervoor. Laten we een voorbeeld nemen:

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

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

Het resultaat is het resultaat dat wordt weergegeven in de schermafbeelding "Leningontleding" gedurende de geanalyseerde periode, die "1" is, dus de eerste periode of de eerste maand.Voor deze betalen we $ 1161. 88, onderverdeeld in $ 856, 20 hoofdsom en $ 305. 68 interesse.

Excel Loan Computation

Nu is het ook mogelijk om de hoofdsom en de renteafbetaling te berekenen voor verschillende perioden, zoals de eerste 12 maanden of de eerste 15 maanden.

= - CUMPRINC (snelheid; lengte; principal; startdatum; einddatum; type)

= - CUMIPMT (snelheid; lengte; principal; startdatum; einddatum; type)

We vinden de argumenten, snelheid, lengte, hoofdsom en term (die verplicht zijn) die we in het eerste deel al zagen met de formule PMT. Maar hier hebben we ook de argumenten start_date en end_date nodig. De eerste geeft het begin van de te analyseren periode aan en de tweede het einde. Laten we een voorbeeld nemen:

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

= - CUMPRINC ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

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

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

Het resultaat is de één weergegeven in de schermafbeelding "Cumul 1st year", dus de geanalyseerde perioden variëren van 1 tot 12, van de eerste periode (eerste maand) tot de twaalfde (12e maand). Over een jaar zouden we $ 10 419, 55 Principal en $ 3 522. 99 Interest betalen.

Aflossing van de lening

Met de voorgaande formules kunnen we onze planningsperiode per periode opstellen, hoeveel we maandelijks in hoofdsom en rente zullen betalen en hoeveel er nog moet worden betaald.

Maak een leenschema in Excel

Om een ​​leenschema te maken, gebruiken we verschillende formules die hierboven zijn besproken en breiden we deze uit over het aantal perioden.

Voer in de kolom eerste periode simpelweg '1' in als eerste periode en sleep de cel vervolgens naar beneden. In ons geval hebben we 120 perioden nodig sinds een 10-jarige uitbetaling van de lening vermenigvuldigd met 12 maanden = 120.

De tweede kolom is het maandelijks bedrag dat we elke maand moeten betalen, wat constant is over het gehele leningschema. Om het te berekenen, plaatst u de volgende formule in de cel van onze eerste periode:

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

De derde kolom is de hoofdsom die maandelijks zal worden terugbetaald. Voor de 40e periode zullen we bijvoorbeeld $ 945 terugbetalen. 51 in principe op ons maandelijks totaalbedrag van $ 1, 161. 88. Om de hoofdsom te innen, gebruiken we de volgende formule:

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

De vierde kolom is de rente, waarvoor we berekenen dat de hoofdsom wordt terugbetaald op ons maandbedrag om te ontdekken hoe veel rente moet worden betaald, met behulp van de formule:

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

De vijfde kolom bevat het bedrag dat nog moet worden betaald. Na de 40e betaling moeten we bijvoorbeeld $ 83, 994. 69 betalen voor $ 120.000. De formule is als volgt:

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

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

De formule gebruikt een combinatie van hoofdsom in een komende periode met de cel met de geleende hoofdsom. Deze periode begint te veranderen wanneer we de cel kopiëren en slepen.Het screenshot hieronder laat zien dat aan het einde van 120 perioden onze lening is terugbetaald.