What are deferred mortgage payments

Use Excel Formulas to Find Payments and Savings

Managing personal finances can be challenging, especially when trying to plan your payments and savings. Excel formulas can help you calculate the future value of your debt and investments, making it easier to determine how long it will take to meet your goals. Use the following functions:

  • RMZ calculates the constant payment of an annuity per period, assuming constant payments and a constant interest rate. (RMZ = regular payment)

  • ZZR calculates the number of payment periods for an investment based on regular, constant payments and a constant interest rate. (ZZR = number of payment periods)

  • BW returns the present value (present value) of an investment. Present value is the total amount that a series of future payments is worth at the present point in time.

  • ZW Returns the future (terminal value) value of an investment based on regular equal payments and a constant interest rate.

Find the monthly installments for paying off credit card debt

For example, suppose the amount owed is $ 5,400 at an annual interest rate of 17%. No further purchases will be made on the card while the debt is being paid off.

Use the RMZ (ZINS; ZZR; BW) function.

= RMZ (17% / 12; 2 * 12; 5400)

The result is a monthly payment of € 266.99 to pay off the debt in two years.

  • The interest rate argument is the interest rate per period on the loan. In this formula z. B. the annual interest rate of 17% divided by 12, the number of months per year.

  • The ZZR (payment period) argument of 2 * 12 is the total number of payment periods for the loan.

  • The BW argument (present value) is 5400.

Find monthly mortgage payments

Assume a house worth € 180,000 with 5% interest and a term of 30 years.

Use the RMZ (ZINS; ZZR; BW) function.

= RMZ (5% / 12; 30 * 12; 180000)

The result is a monthly rate (excluding insurance and taxes) of € 966.28.

  • The interest rate argument is 5% divided by the 12 months of the year.

  • The ZZR argument is 30 * 12 for a term of 30 years with 12 monthly payments per year.

  • The BW argument is 180,000 (the compounded value of the mortgage loan).

Determine the required monthly savings amount for the dream vacation

You want to save on a vacation in three years, which should cost € 8,500. The annual savings interest is 1.5%.

Using the function RMZ (interest; Zzr; Bw; Zw)

= RMZ (1.5% / 12; 3 * 12; 0; 8500)

To save € 8,500 in three years, you would have to save € 230.99 every month for three years.

  • The interest rate argument is 1.5% divided by 12 the number of months per year.

  • The ZZR argument is 3 * 12 for twelve monthly payments over three years.

  • The BW argument (present value) is 0 because the account starts at 0.

  • The ZW (future value) argument that you want to save is $ 8,500.

Now suppose you are saving on a vacation for € 8,500 over three years and are wondering how much you would have to deposit into your account in order to limit the monthly savings amount to € 175.00. The BW function calculates how much starting credit you need for a future value.

Use the function BW (ZINS; ZZR; RMZ; ZW).

= BW (1.5% / 12; 3 * 12; -175; 8500)

A starting credit of € 1,969.62 would be required in order to be able to save € 175.00 per month and to have € 8,500 after three years.

  • The interest rate argument is 1.5% / 12.

  • The ZZR argument is 3 * 12 (or twelve monthly payments over 3 years).

  • The RMZ argument is -175 (you pay € 175 per month).

  • The ZW (future value) is 8,500.

Find out the repayment period for a personal loan

For example, let's say you have a $ 2,500 personal loan and have agreed to a monthly payment of $ 150 with an annual interest rate of 3%.

Use the ZZR (ZINS; RMZ; BW) function

= ZZR (3% / 12; -150; 2500)

It takes 17 months and a few days to repay the loan.

  • The interest rate argument is 3% / 12 monthly payments per year.

  • The RMZ argument is -150.

  • The BW argument (present value) is 2500.

Determine a deposit

For example, let's say you want to buy a car for € 19,000 at 2.9% interest over three years. You want to limit the monthly payments to € 350, so you need to calculate your deposit. In this formula, the result of the BW function is the loan amount, which is then subtracted from the purchase price to get the down payment.

Use the BW (ZINS; ZZR; RMZ) function.

= 19000-BW (2.9% / 12; 3 * 12; -350)

The required down payment is € 6,946.48.

  • The purchase price of € 19,000 is listed first in the formula. The result of the BW function is subtracted from the purchase price.

  • The interest rate argument is 2.9% divided by 12.

  • The ZZR argument is 3 * 12 (or twelve monthly payments over 3 years).

  • The RMZ argument is -350 (you pay € 350 per month).

Development of a savings balance over time

Starting with € 500 in your account, how much do you have after 10 months if you deposit € 200 a month and receive 1.5% interest?

Use the function ZW (ZINS; ZZR; RMZ; BW).

= ZW (1.5% / 12; 10; -200; -500)

In 10 months you would have savings of € 2,517.57.

  • The interest rate argument is 1.5% / 12.

  • The ZZR argument is 10 (months).

  • The RMZ argument is -200.

  • The BW argument (present value) is 500.