How to Use the PPMT Function in Excel

How to Use the PPMT Function in Excel

Do you want to know how much of your principal loan amount you’re paying off? Use the PPMT function in Excel to help you. This guide will explain how.

When you’re paying off a loan, you’ll probably make regular payments of the same amount. Part of each payment will go towards paying off the interest on your loan, while the other part will go towards paying off the amount that you borrowed.

The way these payments are split isn’t the same from month to month, however. The more payments you make, the smaller your interest payments become. As a consequence, the amount that goes towards reducing your loan (the payment on principal) increases every month.

If you want to know the proportion of your loan payment that’s going towards paying off your outstanding balance, you can use the PPMT function in Microsoft Excel to help you.

What is the PPMT Function in Excel?

If you want to know exactly how much of your monthly payment is a payment on principal, you can use the PPMT function in Excel. This function will calculate the payment on principle for any payment of your loan from the first to the last. You’ll need to supply it with some key information, however.

The syntax of the PPMT function is PPMT(rate, per, nper, pv, [fv], [type]) where:

  • Rate (required) is the interest rate per period.
  • Per (required) is the payment period you want to focus on, such as month two.
  • Nper (required) is the total number of payments.
  • Pv (required) is the present value, which for a loan is how much you originally borrowed.
  • Fv (optional) is the future value you want to remain after the last payment. Defaults to zero.
  • Type (optional) indicates if payments are due at the start (1) or end (0) of each period. Defaults to zero.

How to Use the PPMT Function in Excel

The simplest way to use the PPMT function in Excel is to enter all the information about your loan into cells in your spreadsheet. You can then refer to these cells in your formula. By labeling each piece of information you enter, you can easily keep track of what each value represents.

To use the PPMT function in Excel:

  1. Open your Excel spreadsheet.
  2. Enter the interest rate, target payment period, number of payments, and loan amount into your spreadsheet.
    How to Use the PPMT Function in Excel
  3. Select the cell where you want your payment on principal to appear.
  4. Type =PPMT( and select the cell containing your interest rate.
    How to Use the PPMT Function in Excel
  5. If this is an annual rate, and your payments are monthly, type /12 to use the monthly interest rate.
    How to Use the PPMT Function in Excel
  6. Type a comma, then click on the cell containing the target payment period. For example, a value of 1 would calculate the payment on principal value for your first payment. A value of 12 would calculate the payment on principal value for your twelfth payment.
    How to Use the PPMT Function in Excel
  7. Type a comma and select the cell containing the total number of payments. If you are making monthly payments, this would be the number of months rather than the number of years of your loan.
    How to Use the PPMT Function in Excel
  8. Enter another comma and select the loan amount.
  9. Type a closed bracket to complete the formula.
    How to Use the PPMT Function in Excel
  10. Press Enter and the formula will calculate the amount of payment on principal for the month you specified. In this example, in the first month, $147.05 of the original loan amount would be paid off, with the remainder of the monthly payment covering the accrued interest.
    How to Use the PPMT Function in Excel
  11. The result is red and in brackets, indicating that this is an outgoing payment.
  12. Since our formula refers to specific cells rather than values, to find the payment on principal in month 12, we simply change the value of that cell, and the result automatically recalculates.
    How to Use the PPMT Function in Excel
  13. The payment on principal is increased, as less of the monthly payment is used to cover interest payments.

Keeping Track of Your Finances Using Excel

Learning how to use the PPMT function in Excel allows you to make what would otherwise be complex calculations quickly and easily. Once you know how to use the function, you can work out the exact amount of your principal loan you will repay for any and all months that the loan is outstanding.

There are plenty of other useful Excel functions that can save you time and effort. You can use the TRUNC function in Excel to quickly convert decimals into integers. If you’re working with text, you can use the UPPER and LOWER functions to convert the text to upper or lower case, or the PROPER function to convert text into standard capitalization.

Once you’ve created a formula, you can then apply that formula to an entire column.

News Related

OTHER NEWS

iQOO Neo 7 SE Battery, Fast Charging Specifications Confirmed Ahead of December 2 Launch

The iQOO Neo 7 SE will pack a bigger battery than the iQOO Neo 6 SE, which launched in India as the Neo 6 5G. Read more »

Xbox Games with Gold ei vakuuta joulukuussakaan

Xbox Games with Gold ei vakuuta joulukuussakaan Microsoft on jo kauan päättänyt panostaa ennen kaikkea Xbox Game Passiin jättäen Xbox Liven tilaajien Xbox Games with Gold -edut vähän kuin lapsipuolen ... Read more »

Mercedes GLB Engine Specifications, Mileage, Power, Torque

Mercedes GLB Engine Specifications, Mileage, Power, Torque This article will give you a perspective of the engine specs, such as displacement, power, torque, mileage and transmission details, of the Mercedes ... Read more »

Ask the Expert: How to fix dehydration, dry skin and chafing in winter

When winter rolls around some of us find the colder weather brings with it dehydration, dry skin and, in some cases, chafing skin. The season is no friend to our ... Read more »

Fraude à la « fausse réparation informatique » : un réseau qui avait piégé 4000 victimes a été démantelé

Selon le parquet de Paris, le préjudice est évalué à 1,2 million d’euros pour 4000 victimes recensées. (Illustration). AFP/ISSOUF SANOGO. Il valait mieux ne pas appeler le numéro de téléphone ... Read more »

Toyota Innova Electric Spied On Public Road For First Time

Innova Crysta electric concept prototype resembles its ICE-powered sibling with a closed-off grille at the front and blue accents Toyota Innova Crysta Electric Concept Prototype We have seen Toyota going ... Read more »

How to view Large Files on Windows effortlessly?

Are you looking for a solution to open and view big files on Windows PC? This post will help you view large files effortlessly. Here, we will be listing some ... Read more »

How to get Chipped Pot and Cracked Pot in Pokémon Scarlet & Violet

Screenshot by Pro Game Guides Chipped Pot and Cracked Pot are held items that will let you evolve your Sinistea into Polteageist. Polteageist is a Ghost-type Pokémon first introduced in ... Read more »

Google Pixel 7a First Renders Leaked; Check Them Out!

Now that the high-end Pixel 7 series is out, it’s time for Google to focus on its next-gen mid-range phone, possibly called the Pixel 7a. We now have the first ... Read more »

How to Fix the “SIM Not Provisioned MM 2” Error (8 Methods)

Just bought a new smartphone? But, unable to make calls or use the internet due to an unknown SIM card error? If this happens to you, you have landed on ... Read more »

Disney rejects Apple takeover rumours again

Disney is going through a very hard time right now. Due to several issues, the company had to fire its CEO, Bob Chapek was replaced by Bob Iger. For the latter, it ... Read more »

Microsoft Surface Laptop 5 Price in Malaysia & Specs

The Microsoft Surface Laptop 5 is powered by an 12th Gen Intel® Core™ i5-1235U processor, Intel Iris Xe graphics, 8GB LPDDR5x RAM and a 256GB SSD. It also has a ... Read more »

Το τέλος των social media όπως τα ξέραμε (;)

Το τέλος των social media όπως τα ξέραμε (;) *Του Χάρη Παπαευαγγέλου, Υποψήφιου Διδάκτωρα στην Πολιτική Οικονομία της Διακυβέρνησης των Ψηφιακών Πλατφορμών στο Πανεπιστήμιο της Τουλούζης – Ανάλυση στο Ινστιτούτο ... Read more »

Mercedes-Benz GLB & EQB EV 7-seat SUV First Drive Review | The First Impression | Nov 2022

Nov 2022: Team V3Cars gives you a detailed review of the soon-to-be-launched Mercedes-Benz GLB & EQB EV SUVs. The GLB is the smallest 3-row Mercedes-Benz car, and it will be ... Read more »

昔懐かしの「こたつコード」がUSBケーブルに、中間スイッチもあるぞ

ミヨシは11月30日、スイッチ付きのUSB延長ケーブル「USB-EXS3シリーズ」を発表した。ケーブル長は0.5m、1m、1.5mの3種を用意し、11月下旬から順次販売する。価格はオープンで、店頭予想価格は0.5mが1,408円前後、1mが1,518円前後、1.5mが1,628円前後。 こたつコード風デザインが懐かしすぎる、「スイッチ付きUSB延長ケーブル USB-EXS3」シリーズ 「USB-EXS3シリーズ」は、こたつコードのようなレトロ調のデザインを採用し、最大5V3Aまでの給電に対応したUSB延長ケーブル。ケーブル途中には中間スイッチも設け、接続したUSB機器をスイッチでオン/オフできる。 中間スイッチでは、USB機器の電源のオン/オフに加え、HDDやUSBメモリといったストレージの接続/切断(Windows 10 Ver.1809以降、およびWindows 11に対応)や、スマートフォンへの給電オン/オフなどに対応する。 接続したUSB機器のオン/オフのほか、HDDやUSBメモリの接続/切断も可能 USB規格はUSB 3.0で、最大5Gbps(規格値)までのデータ通信が行える。コネクタ形状はUAB A(オス) – USB A(メス)。 ケーブル長は0.5m、1m、1.5mの3種類を用意 Read more »

2023 Hyundai Santa Fe Hybrid review: First Australian drive

8.5/10Score Score breakdown 8.5 Safety, value and features 9.0 Comfort and space 8.0 Engine and gearbox 8.5 Ride and handling 8.0 Technology Things we like Engine refinement Upholstery colour choices ... Read more »

Hyundai Santa Fe Hybrid Highlander 2023 review

New hybrid engine brings better fuel economy plus desirable turbo-petrol-AWD combination to the Santa Fe for the first time Read more »

How to reset Pokémon spawns in Pokémon Scarlet & Violet – Radar Trick

Screenshot by Pro Game Guides If you’re hunting for a certain Pokémon around the area, you may be curious as to how to get them to spawn. In this guide, ... Read more »

Xbox-pomo Phil Spencerin mukaan pelien myyntiä ei lopeteta huomion siirtämiseksi Xbox Game Passiin

Xbox-pomo Phil Spencerin mukaan pelien myyntiä ei lopeteta huomion siirtämiseksi Xbox Game Passiin Xbox Game Pass on ajan saatossa kasvanut tärkeäksi Microsoftin liiketoiminnalle Xboxin suhteen. Toisaalta Microsoft ei aio panostaa ... Read more »

Mercedes-Benz EQB First Drive Review

Put simply, the Mercedes-Benz EQB is perhaps the most practical electric vehicle in India right now. It has seven seats, a claimed range of over 400km, classy interiors, and at ... Read more »

OnePlus Nord CE 3 5G Design Renders Surface Online Ahead of Official Launch, Show off Circular Camera Module

The OnePlus Nord CE 3 will get a major redesign compared to its predecessor. Read more »

Vivo Y02 With Octa-Core Processor and 5000mAh Battery Launched: Price, Specifications

The Vivo Y02 offers an 8MP rear shooter and a 5MP front camera. Read more »

Kim Kardashian and Kanye West settle divorce, with $200,000 per month in child payments

Kim Kardashian and Kanye West, who is now known as Ye, have reached a settlement in their divorce, averting a trial that had been set for next month, court documents ... Read more »

Microsoft se abriría realizar “concesiones” con el fin de desbloquear la compra de Activision Blizzard

Foto: Reuters. Pasa el tiempo y la compra de Activision Blizzard por parte de Microsoft aún no avanza. Sin embargo recientes reportes apuntarían a que la compañía podría abrirse a ... Read more »

Upcoming Electric Scooters, Motorcycles – Honda, Yamaha, Ola, KTM

Upcoming electric two wheelers that are likely to launch in 2023 are both from mainstream manufacturers and startups alike Upcoming Yamaha Electric scooters India 2023. Image – Nikam Yamaha. Even ... Read more »

Pokemon Scarlet and Violet Guide – All Evolution Items, and How to Evolve Pokemon

Confused about how to evolve an Eevee into Umbreon or Gimmighoul into Gholdengo? Check out all the Evolution requirements here. Read more »

StarHub beefs up enterprise offerings with Cloud platform and automation processes

Local telco StarHub has rolled out new enterprise targeted workplace solutions. Firstly, offering a managed cloud networking service; and second, and automated onboarding process and access to an automation platform. ... Read more »

PICO #TheBestXmasGift: Get the PICO 4 VR headset with 4 additional free games and extra gifts from just RM1599

PICO Malaysia is offering special deals in conjunction with the upcoming Christmas celebration to make it #TheBestXmasGift for virtual reality (VR) enthusiasts in our country. Happening from 3 to 25 December ... Read more »

Workday (WDAY) Q3 2023 Earnings Call Transcript

Logo of jester cap with thought bubble. Workday (NASDAQ: WDAY) Q3 2023 Earnings Call Nov 29, 2022, 4:30 p.m. ET Contents: Prepared Remarks Questions and Answers Call Participants Prepared Remarks: Operator Welcome ... Read more »

The Best, Coolest New Gadgets of November 2022

November 2022 is in the books. We look back on all the coolest gadgets that dropped this month. Read more »

Samsung Galaxy S23 Ultra FCC Certification Confirms S-Pen Support, Launch Expected Soon

Samsung Galaxy S23 Ultra will come equipped with a 4855mAH battery unit. Read more »

Fridge freezers are ‘one of the most costly appliances' - how to avoid ‘excessive bills'

As the cost of living crisis looms over households in both energy bills and spending costs, many will be looking to cut costs wherever they can. One way people can ... Read more »

Sur les marchés boursiers, l’âge d’or des entreprises technologiques est terminé, selon Goldman Sachs

Pendant près d’une décennie, le marché boursier américain a été dominé par des géants du secteur de la technologie tels qu’Alphabet, Microsoft, Amazon ou encore Apple. Une ère qui semble ... Read more »

General Motors reportedly creating electric-powered Corvette sub-brand

Reports indicate that both a four-door coupe and an SUV have been proposed at GM, but under the performance Corvette name  Read more »

Windows 11 KB5020044 Update Is Now Available — Here’s What You Need To Know

Microsoft’s last preview update for Windows 11 is now up for grabs. The Redmond-based tech giant recently released Windows 11’s optional KB5020044 update, which comes with more than a handful ... Read more »

Samsung Galaxy Z foldable phones see doubled demand among enterprise customers

Samsung must be very pleased with how well its foldable handsets are doing. According its official statement, Samsung saw a 105% increase in the number of Galaxy Z Fold and ... Read more »

Lancia has been reborn as a pure electric brand, will launch three new EVs by 2028

If you are of a certain age, you'll know the name Lancia and probably even remember the time when cars like the Dedra still plied our roads. The famed Italian ... Read more »

Shoot easily with Samsung FlexCam, and enjoy extra features with Flex Mode on the Samsung Galaxy Z Flip4!

In recent years, foldable smartphones have gradually become popular in Malaysia. Among them, Samsung has positioned the price of its models at around RM4000, lowering the barrier to entry for ... Read more »

Record of Ragnarok Season 2 launch date confirmed; Check details

Record of Ragnarok anime Season 1 ended with a cliffhanger which left the fans excited for the second season. Now, the latest trailer of the Record of Ragnarok Season 2 ... Read more »

Samsung Galaxy A14 5G Moniker Confirmed by NBTC and Bluetooth SIG Certifications, Launch Expected Soon

The Samsung Galaxy A14 5G might be powered by Exynos 1330 processor. Read more »
Breaking thailand news, thai news, thailand news Verified News Story Network